Prev: Number Truncation problem
Next: Siebel or Oracle CRM on Demand - NC& MA - 10+months
From: contacts on 13 Jul 2010 11:18
I have a HUGE excel file of codes that I need to refer to in order to
define a variable. For example, there is a file of about 1000 codes
in excel, and I want to say "if code=: 'xxxx' then code1='Y'; So the
"code" variable in excel must be accessed. Is there any way to refer
to this code table without actually cutting and pasting the 1000 codes
into SAS?

Thanks so much!
From: Ya on 13 Jul 2010 12:40
On Jul 13, 8:18 am, contacts <jcer...(a)gmail.com> wrote:
> I have a HUGE excel file of codes that I need to refer to in order to
> define a variable.  For example, there is a file of about 1000 codes
> in excel, and I want to say "if code=: 'xxxx' then code1='Y';  So the
> "code" variable in excel must be accessed.  Is there any way to refer
> to this code table without actually cutting and pasting the 1000 codes
> into SAS?
>
> Thanks so much!

Try to read in the Excel file using proc import. After you get the SAS
dataset version of the data,
you can apply your logic in a data step.

HTH

Ya
From: Reeza on 13 Jul 2010 13:55
On Jul 13, 8:18 am, contacts <jcer...(a)gmail.com> wrote:
> I have a HUGE excel file of codes that I need to refer to in order to
> define a variable.  For example, there is a file of about 1000 codes
> in excel, and I want to say "if code=: 'xxxx' then code1='Y';  So the
> "code" variable in excel must be accessed.  Is there any way to refer
> to this code table without actually cutting and pasting the 1000 codes
> into SAS?
>
> Thanks so much!

Is the excel file a lookup table ie has code and code1 or just code?

You may want to consider creating a lookup table or a format rather
than write 1000 if then statements.

HTH,
Reeza
From: Richard A. DeVenezia on 14 Jul 2010 14:31
On Jul 13, 11:18 am, contacts <jcer...(a)gmail.com> wrote:
> I have a HUGE excel file of codes that I need to refer to in order to
> define a variable.  For example, there is a file of about 1000 codes
> in excel, and I want to say "if code=: 'xxxx' then code1='Y';  So the
> "code" variable in excel must be accessed.  Is there any way to refer
> to this code table without actually cutting and pasting the 1000 codes
> into SAS?
>
> Thanks so much!

You can Proc IMPORT the codes from the Excel file and execute a SQL
query to perform the existential lookup. EQT operates as a prefix
comparator.

----------
/*
proc import ... out=myCodes;
*/

data myCodes; * a simulation;
input code $4. @@;
if not missing (code);
datalines;
aaaabbbbababcdcdefefbaca
run;

data have;
do id = 1 to 1000;
length code $10;
code = cat
( byte(rank('a') + 7*ranuni(1234))
, byte(rank('a') + 7*ranuni(1234))
, byte(rank('a') + 7*ranuni(1234))
, byte(rank('a') + 7*ranuni(1234))
, byte(rank('a') + 7*ranuni(1234))
);
output;
end;
run;

proc sql;
create table want as
select
*
, case
when exists (select * from myCodes where have.code EQT
myCodes.code) then 'Y'
else ' '
end as codeFound
from have
;
quit;
----------

Richard A. DeVenezia
http://www.devenezia.com
 | 
Pages: 1
Prev: Number Truncation problem
Next: Siebel or Oracle CRM on Demand - NC& MA - 10+months