IF then statement with proc sql

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

IF then statement with proc sql

Greetings and happy holidays all, I have a question about my if statement at the bottom of my code.  I want my logic to say, first check if there are two or more Unique ID's in Table if there are 2 or more

Unique Id's then I need to create the new tables to be sampled based off of TableA where the Unique_ID = unique_id1 or unique_id2.  If not (Else) proceed to next step (sampling).  Right now I keep getting an error on the "stopping" part of my else code, the log says that it's not a valid if-then statement. 

Is this possible?  I feel like my code is getting really messy.  How else could I work this?  I'm up for any suggestions as I currently have 3 chrome browsers open with 20 tabs on each. 

/*Find how many Unique ID's there are*/

proc sql;

  SELECT COUNT(distinct UNIQUE_ID)

  INTO :UniqueIDs

  FROM TableA;

  %put &UniqueIDs;

quit;

/*if there are two or more unique ID's create tables based on them*/

Data _null_;

  if &UniqueIDs > 1 then;

   proc sql;

  SELECT distinct UNIQUE_ID

  INTO :unique_id1 - :unique_id2

  FROM TableA;

  %put &unique_id1 &unique_id2;

  Data tableuniq1;

  set TableA;

  if Unique_ID EQ: "&unique_id1";

  Data tableuniq2;

  set TableA;

  if Unique_ID EQ: "&unique_id2";

  ELSE;

  if &UniqueIDs <= 1 stop;

  %samplesizemacro (TableA, target);

  proc surveyselect data = TableA

  n=&samplesizemacro seed=&random out = TableA;

  run;

Best Regards,

Ben


Accepted Solutions
Solution
‎12-31-2014 01:02 PM
Grand Advisor
Posts: 17,360

Re: IF then statement with proc sql

Any amount of valid SAS code can go between %do and %end, in your case, both data steps can be in one %do/%end block.

There is no need for the %else since there's no other condition you're executing, so you can remove the %else;%do;%end; from your code.

You're also not creating &uniqueIDs within the macro which seems a little weird to me. What if there are more than 2 ids in your first proc sql, what happens to the macro variables?

There is no run at the end of your data steps so I don't know if that's interfering anywhere, though I doubt it.

This should give you the same results you originally had.

%macro multiuid();

proc sql;

SELECT distinct UNIQUE_ID

INTO :unique_id1 - :unique_id2

FROM TableA;

%put &unique_id1 &unique_id2;

quit;

%if &UniqueIDs>=2 %then %do;

  Data TableAuniq1;

  set TableA;

  if Unique_ID EQ: "&unique_id1";

run;

  Data TableAuniq2;

  set TableA;

  if Unique_ID EQ: "&unique_id2";

run;

%end;

%mend;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,203

Re: IF then statement with proc sql

Hi,


See if it works, just added then before stop.


if &UniqueIDs <= 1 then stop;

Grand Advisor
Posts: 17,360

Re: IF then statement with proc sql

Do you think that last data _null_ step is valid SAS/macro code?

Occasional Contributor
Posts: 18

Re: IF then statement with proc sql

It may not be, I'm not sure how to work the data_null_ part into this because I don't want to create tables that don't need to be created.  That's why I used Data_null_.  Can I make this whole thing a data step instead?  If so, can you point me in the right direction of something like that?

Also, I'm not understanding why each time I try running this code it produces a tableuniq1, but the code says to skip that part if &UniqueIDs <= 1; (if &UniqueIDs <= 1 then stop).

Grand Advisor
Posts: 17,360

Re: IF then statement with proc sql

Its not specified appropriately based on SAS syntax.

You probably want to be using macro logic for one, not data step logic.

%macro something();

proc sql;

  SELECT COUNT(distinct UNIQUE_ID)

  INTO :UniqueIDs

  FROM TableA;

quit;

%if &UniqueIDs>=2 %then %do;

*valid sas code;

%end;

%else %do;

*valid sas code;

%end;

%mend;

%something;

Occasional Contributor
Posts: 18

Re: IF then statement with proc sql

Hmmm.. ok, that makes sense.  For the "valid sas code" that you wrote... is that the data step like:

  Data tableuniq1;

  set TableA;

  if Unique_ID EQ: "&unique_id1";

??

Grand Advisor
Posts: 17,360

Re: IF then statement with proc sql

The SAS code you'd like to execute based on that logic. So yes.

Occasional Contributor
Posts: 18

Re: IF then statement with proc sql

Thanks again, and last question... is there a reason why I should use a Macro for this instead of data step logic? 

I'm guessing it's because I'm not always going to be performing this step, and therefore I should make a macro that does it on the occasions I need to vs always executing the steps?  For the same reason that I had wrote about when I said "I only want to create the tabels if I have to, not each time"...

Just curious.

Grand Advisor
Posts: 17,360

Re: IF then statement with proc sql

You use a macro instead of data step because you need to conditionally execute code, which are full blocks data steps/procs.

You can do that with a data step but using call execute. Given your particular parameters using call execute to generate the code would be more difficult.

You can't nest datasteps/proc sql within each other the way you were attempting to.

Occasional Contributor
Posts: 18

Re: IF then statement with proc sql

Many thanks Reeza,

Back to the code; I used the format that you had written out for me like this:

%macro multiuid();

proc sql;

SELECT distinct UNIQUE_ID

INTO :unique_id1 - :unique_id2

FROM TableA;

%put &unique_id1 &unique_id2;

quit;

%if &UniqueIDs>=2 %then

%do;

  Data TableAuniq1;

  set TableA;

  if Unique_ID EQ: "&unique_id1";

%end;

%else; %do; %end;

%if &UniqueIDs>=2 %then

%do;

  Data TableAuniq2;

  set TableA;

  if Unique_ID EQ: "&unique_id2";

%end;

%else; %do; %end;

%mend;

The first data step goes well, the Macro is able to create the new table (TableAuniq1), but it stumbles when it tries to create the second table.  Even when I break it into another %if statement.  Can I trick it to run the test again like this? I've tried unsuccessfully.  How can I run multiple data step statements in a %Do part?  I've read up on Do groups, but they only seem to have one data step before the else statement.

%if &UniqueIDs>=2 %then

%do;

  Data TableAuniq2;

  set TableA;

  if Unique_ID EQ: "&unique_id2"

%end;

Any help appreciated! Happy NYE!

Solution
‎12-31-2014 01:02 PM
Grand Advisor
Posts: 17,360

Re: IF then statement with proc sql

Any amount of valid SAS code can go between %do and %end, in your case, both data steps can be in one %do/%end block.

There is no need for the %else since there's no other condition you're executing, so you can remove the %else;%do;%end; from your code.

You're also not creating &uniqueIDs within the macro which seems a little weird to me. What if there are more than 2 ids in your first proc sql, what happens to the macro variables?

There is no run at the end of your data steps so I don't know if that's interfering anywhere, though I doubt it.

This should give you the same results you originally had.

%macro multiuid();

proc sql;

SELECT distinct UNIQUE_ID

INTO :unique_id1 - :unique_id2

FROM TableA;

%put &unique_id1 &unique_id2;

quit;

%if &UniqueIDs>=2 %then %do;

  Data TableAuniq1;

  set TableA;

  if Unique_ID EQ: "&unique_id1";

run;

  Data TableAuniq2;

  set TableA;

  if Unique_ID EQ: "&unique_id2";

run;

%end;

%mend;

Occasional Contributor
Posts: 18

Re: IF then statement with proc sql

Wow, that's very clean thank you! As for the &unique_id1, 2, 3.. there will never be more than two, at least for now.  If there ends up being three I should probably add the select distinct into a seperate macro correct?  One thing that will always happen in the macro that I've created now is the unique_id1 will always be assigned a value, which I don't know if is good or bad. 

This helps me a ton, thanks again Rezza, I'm going to work on the next phase of this, and probably will be back when I get stuck Smiley Happy

Happy New Year!

Esteemed Advisor
Posts: 7,294

Re: IF then statement with proc sql

If has already answered your question you really ought to give her credit for it by marking her answer as being correct. Also, Happy New Year to you and all!

Community Manager
Posts: 2,696

Re: IF then statement with proc sql

Here's another neat trick.  You can get PROC SQL to create entire SAS statements -- or even entire DATA steps -- for each unique value in your data set column.

Here's a sample with the SASHELP.CARS data set.  Running this program will create 3 output data sets -- one for each value of Origin.  It splits the data set...which I'm guessing is your ultimate goal in this program.

proc sql;
/* build a mini program for each value */
/* create a table with valid chars from data value */
select distinct cats('DATA table',compress(origin,,'kad'),
             
'; set SASHELP.CARS; IF origin="', origin,
             
'"; run;') into :allsteps separated by ';'
 
from sashelp.cars;

quit;

%macro runSteps;
&
allsteps.;
%mend;

%
runSteps;

This program actually generates the following SAS code:

DATA tableAsia; set SASHELP.CARS; IF origin="Asia"; run;                                                                           

DATA tableEurope; set SASHELP.CARS; IF origin="Europe"; run;                                                                       

DATA tableUSA; set SASHELP.CARS; IF origin="USA"; run;                                                                             

Some notes:

  • I used the COMPRESS function to include only alphanumeric characters in the output table names.  (I could further wrap that in a SUBSTR to ensure the table name doesn't exceed 32 characters.)
  • I used the CATS function to create new values (DATA steps) that include the values of each Origin.
  • The result is a macro variable (&ALLSTEPS) that contains the program to run.  I then included that result in the %macro block.

There are definitely more efficient ways to achieve the same result, including a single DATA step that would allow for one pass through the data.  But I hope that you find the technique here to be useful.

Chris

Community Manager
Posts: 2,696

Re: IF then statement with proc sql

I've just captured some of this content into a blog post -- hoping it helps for future inquiries:

Chris

☑ This topic is SOLVED.

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

Discussion stats
  • 14 replies
  • 758 views
  • 2 likes
  • 5 in conversation