BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Jahanzaib
Quartz | Level 8

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

@Jahanzaib Please mark the correct answer to set the question as solved. 

View solution in original post

16 REPLIES 16
Shmuel
Garnet | Level 18

data want;

  set have;       

if 0100<=SICC<=0999 then FFI12=1; else

if 3990<=SICC<=3999 then FFI12=2else

if 2520<=SICC<=2589 then FFI12=3else

if 2900<=SICC<=2999 then FFI12=4else

if 2840<=SICC<=2899 then FFI12=5else

if 7370<=SICC<=7379 then FFI12=6else

if 4800<=SICC<=4899 then FFI12=7else

if 4900<=SICC<=4949 then FFI12=8else

if 5000<=SICC<=5999 then FFI12=9else

if 3840<=SICC<=3859 then FFI12=10else

if 6000<=SICC<=6999 then FFI12=11else FFI12 = 12;

 

run;

Shmuel
Garnet | Level 18
there more ways to do it:
- using format
- using select

Sorry, I'm short in time to enter details now.
Reeza
Super User

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

 

 

PeterClemmensen
Tourmaline | Level 20

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;
Reeza
Super User

@PeterClemmensen shouldn't it be 

 

when in (100:999) 

 

Colon rather than comma. 

PeterClemmensen
Tourmaline | Level 20

ah yes of course @Reeza, nice spotted 🙂

mkeintz
PROC Star

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

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
PeterClemmensen
Tourmaline | Level 20

@mkeintz havn't tested it but you are probably right 🙂

Kurt_Bremser
Super User

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.

mkeintz
PROC Star

@Jahanzaib:

 

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

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Jahanzaib
Quartz | Level 8

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?

 

mkeintz
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 16 replies
  • 6551 views
  • 8 likes
  • 6 in conversation