if 0100<=SICC<=0999 then FFI12=1;
if 3990<=SICC<=3999 then FFI12=2;
if 2520<=SICC<=2589 then FFI12=3;
if 2900<=SICC<=2999 then FFI12=4;
if 2840<=SICC<=2899 then FFI12=5;
if 7370<=SICC<=7379 then FFI12=6;
if 4800<=SICC<=4899 then FFI12=7;
if 4900<=SICC<=4949 then FFI12=8;
if 5000<=SICC<=5999 then FFI12=9;
if 3840<=SICC<=3859 then FFI12=10;
if 6000<=SICC<=6999 then FFI12=11;
if SICC='.' then FFI12=12; else FFI12=12; run;
I got stuck in this last point. the very last statement is not correct. I want to make the last statement that if SICC do not have any value or do not lie in above 11 categories then FFI12 turn to 12.
@Jahanzaib Please mark the correct answer to set the question as solved.
data want;
set have;
if 0100<=SICC<=0999 then FFI12=1; else
if 3990<=SICC<=3999 then FFI12=2; else
if 2520<=SICC<=2589 then FFI12=3; else
if 2900<=SICC<=2999 then FFI12=4; else
if 2840<=SICC<=2899 then FFI12=5; else
if 7370<=SICC<=7379 then FFI12=6; else
if 4800<=SICC<=4899 then FFI12=7; else
if 4900<=SICC<=4949 then FFI12=8; else
if 5000<=SICC<=5999 then FFI12=9; else
if 3840<=SICC<=3859 then FFI12=10; else
if 6000<=SICC<=6999 then FFI12=11; else FFI12 = 12;
run;
You need if then else across all conditions. Right now the first set of ifs are etched check individually but the last one is grouped and erases your previous value. So all become 12.
if SICC='.' then FFI12=12;
else FFI12=12;
Also, you check for missing as a period, without quotes.
Is your SICC variable a character or number? It has leading 0s?
if 0100<=SICC<=0999 then FFI12=1;
Else if 3990<=SICC<=3999 then FFI12=2;
Else if 2520<=SICC<=2589 then FFI12=3;
Else if 2900<=SICC<=2999 then FFI12=4;
Else if 2840<=SICC<=2899 then FFI12=5;
Else if 7370<=SICC<=7379 then FFI12=6;
Else if 4800<=SICC<=4899 then FFI12=7;
Else if 4900<=SICC<=4949 then FFI12=8;
Else if 5000<=SICC<=5999 then FFI12=9;
Else if 3840<=SICC<=3859 then FFI12=10;
Else if 6000<=SICC<=6999 then FFI12=11;
else FFI12=12;
Using a select statement
data want;
set have;
select (SICC);
when in (0100:0999) FFI12=1;
when in (3990:3999) FFI12=2;
when in (2520:2589) FFI12=3;
when in (2900:2999) FFI12=4;
when in (2840:2899) FFI12=5;
when in (7370:7379) FFI12=6;
when in (4800:4899) FFI12=7;
when in (4900:4949) FFI12=8;
when in (5000:5999) FFI12=9;
when in (3840:3859) FFI12=10;
when in (6000:6999) FFI12=11;
otherwise FFI12=12;
end;
run;
Note how clean and visually pleasing @PeterClemmensen's code looks compared to the if-then-else-if avalanche.
ah yes of course @Reeza, nice spotted 🙂
I don't get this syntax to work. the log says:
3392 when in (0100, 0999) FFI12=1;
-- -----
79 22
ERROR 79-322: Expecting a (.
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <,
<=, <>, =, >, ><, >=, AND, EQ, GE, GT, IN, LE, LT, MAX, MIN, NE, NG, NL, NOTIN,
OR, ^=, |, ||, ~=.
I believe you always have to have the WHEN clause as WHEN (condition) action
@mkeintz havn't tested it but you are probably right 🙂
When using ranges, one hast to modify the select:
data want;
set have;
select;
when (sicc in (0100:0999)) FFI12=1;
when (sicc in (3990:3999)) FFI12=2;
when (sicc in (2520:2589)) FFI12=3;
when (sicc in (2900:2999)) FFI12=4;
when (sicc in (2840:2899)) FFI12=5;
when (sicc in (7370:7379)) FFI12=6;
when (sicc in (4800:4899)) FFI12=7;
when (sicc in (4900:4949)) FFI12=8;
when (sicc in (5000:5999)) FFI12=9;
when (sicc in (3840:3859)) FFI12=10;
when (sicc in (6000:6999)) FFI12=11;
otherwise FFI12=12;
end;
run;
Tested.
Two comments on your topic.
First, we often set up SIC to Fame French conversions as functions, rather than statement groups. More about that later.
Array Lookup:
Second, we use array lookup for these conversions. In your case (which doesn't include all the SIC codes ours FF12 does), it would be:
data want;
array s_to_ff12 {100:7379} _temporary_ (900*1,1520*.,70*3,250*.,60*5,100*4,840*.,
20*10,130*.,10*2,800*.,100*7,50*8,
50*.,1000*9,1000*11,370*.,10*6);
if 100<=sicc<=7379 then ff12=sic_to_ff12{sicc};
else ff12=12;
I've rearranged the list by SIC code, rather than FF12 as in your program. This allows the parenthesized list of values to populate the FF12 codes for every element in the S_TO_FF12 array, i.e. SIC from 100 to 7379. For example, it starts out with 900 1's (for sic 100-999), then 1520 missings (sic 1000-2519), etc. Then just ask for the array element corresponding to the SIC code. Faster, and neater than a series of IF THEN ... ELSE, or even SELECT groups.
Good place for a function:
Neater still, if you do a one-time compile and store a function named S_to_FF12 in your MYLIB library in a MYUTILS catalog you could use that function in any subsequent use of sas, without re-compiling. You don't even have to construct the array anymore.
As long as you have an options statement like:
options cmplib=(mylib.myutils);
telling SAS where to look for user-defined functions, then you simply need one statement:
ff12=s_to_ff12(sicc);
And you could also use this function to subset data via a WHERE expression, as in
PROC REG data=mylib.mydata;
where s_to_ff12(sicc) =2;
model ret=sp500 + factor1 +factor2 + factor3;
or
DATA new;
set have;
where s_to_ff12(sicc) in (2,4,6);
So, here's how to make the function, using the array logic above:
proc fcmp outlib=mylib.ffutils.funcs;
deletefunc s_to_ff12;
quit;
proc fcmp outlib=mylib.ffutils.funcs;
function s_to_ff12(_sic);
array ff {100:7379} _temporary_ (900*1,1520*.,70*3,250*.,60*5,100*4,840*.,
20*10,130*.,10*2,800*.,100*7,50*8,
50*.,1000*9,1000*11,370*.,10*6);
if (100<=_sic<=7379) then return(ff{_sic});
else return(.);
endsub;
quit;
That's it.
regards,
Mark
data d0;
set d1;
if 0100<=SICC<=0999 then FFI12=1; else
if 2000<=SICC<=2399 then FFI12=1; else
if 2700<=SICC<=2749 then FFI12=1; else
if 2770<=SICC<=2799 then FFI12=1; else
if 3100<=SICC<=3199 then FFI12=1; else
if 3940<=SICC<=3989 then FFI12=1;else
if 2500<=SICC<=2519 then FFI12=2; else
if 2590<=SICC<=2599 then FFI12=2; else
if 3630<=SICC<=3659 then FFI12=2; else
if 3710<=SICC<=3711 then FFI12=2; else
if 3714<=SICC<=3714 then FFI12=2; else
if 3716<=SICC<=3716 then FFI12=2; else
if 3750<=SICC<=3751 then FFI12=2; else
if 3792<=SICC<=3792 then FFI12=2; else
if 3900<=SICC<=3939 then FFI12=2; else
if 3990<=SICC<=3999 then FFI12=2; else
if 2520<=SICC<=2589 then FFI12=3; else
if 2600<=SICC<=2699 then FFI12=3; else
if 2750<=SICC<=2769 then FFI12=3; else
if 3000<=SICC<=3099 then FFI12=3; else
if 3580<=SICC<=3629 then FFI12=3; else
if 3700<=SICC<=3709 then FFI12=3; else
if 3712<=SICC<=3713 then FFI12=3; else
if 3715<=SICC<=3715 then FFI12=3; else
if 3717<=SICC<=3749 then FFI12=3; else
if 3752<=SICC<=3791 then FFI12=3; else
if 3793<=SICC<=3799 then FFI12=3; else
if 3830<=SICC<=3839 then FFI12=3; else
if 3860<=SICC<=3899 then FFI12=3; else
if 1200<=SICC<=1399 then FFI12=4; else
if 2900<=SICC<=2999 then FFI12=4; else
if 2800<=SICC<=2829 then FFI12=5; else
if 2840<=SICC<=2899 then FFI12=5; else
if 3570<=SICC<=3579 then FFI12=6; else
if 3660<=SICC<=3692 then FFI12=6; else
if 3694<=SICC<=3699 then FFI12=6; else
if 3810<=SICC<=3829 then FFI12=6; else
if 7370<=SICC<=7379 then FFI12=6; else
if 4800<=SICC<=4899 then FFI12=7; else
if 4900<=SICC<=4949 then FFI12=8; else
if 5000<=SICC<=5999 then FFI12=9; else
if 7200<=SICC<=7299 then FFI12=9; else
if 7600<=SICC<=7699 then FFI12=9; else
if 2830<=SICC<=2839 then FFI12=10; else
if 3693<=SICC<=3693 then FFI12=10; else
if 3840<=SICC<=3859 then FFI12=10; else
if 8000<=SICC<=8099 then FFI12=10; else
if 6000<=SICC<=6999 then FFI12=11; else FFI12=12;
run;
Can anyone tell me what is the problem in this statement?
Why don't you tell us the symptom. It's faster and more likely to get good answers, than expecting the respondent to be a mind reader.
@Jahanzaib wrote:
Can anyone tell me what is the problem in this statement?
It makes my eyes want to turn back into my head.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: