DATA Step, Macro, Functions and more

Basics

Accepted Solution Solved
Reply
Contributor
Posts: 72
Accepted Solution

Basics

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.

 


Accepted Solutions
Solution
‎11-29-2016 05:19 AM
Super User
Posts: 19,789

Re: Basics

Posted in reply to Jahanzaib

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

View solution in original post


All Replies
Trusted Advisor
Posts: 1,560

Re: Basics

Posted in reply to Jahanzaib

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;

Trusted Advisor
Posts: 1,560

Re: Basics

there more ways to do it:
- using format
- using select

Sorry, I'm short in time to enter details now.
Super User
Posts: 19,789

Re: Basics

Posted in reply to Jahanzaib

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

 

 

PROC Star
Posts: 740

Re: Basics

[ Edited ]
Posted in reply to Jahanzaib

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;
Super User
Posts: 7,779

Re: Basics

Note how clean and visually pleasing @draycut's code looks compared to the if-then-else-if avalanche.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 19,789

Re: Basics

Posted in reply to KurtBremser

@draycut shouldn't it be 

 

when in (100:999) 

 

Colon rather than comma. 

PROC Star
Posts: 740

Re: Basics

ah yes of course @Reeza, nice spotted Smiley Happy

Trusted Advisor
Posts: 1,019

Re: Basics

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

 

PROC Star
Posts: 740

Re: Basics

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

Super User
Posts: 7,779

Re: Basics

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Trusted Advisor
Posts: 1,019

Re: Basics

Posted in reply to Jahanzaib

@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

Contributor
Posts: 72

Re: Basics

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?

 

Trusted Advisor
Posts: 1,019

Re: Basics

Posted in reply to Jahanzaib

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.

Super User
Posts: 7,779

Re: Basics

Posted in reply to Jahanzaib

Jahanzaib wrote:

Can anyone tell me what is the problem in this statement?

 


It makes my eyes want to turn back into my head.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

Need further help from the community? Please ask a new question.

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