topic Re: sql in SAS Procedures

sql

deleted_user — Thu, 10 Mar 2011 16:44:28 GMT

Hi ,

I have created a table with AGEge7 and need to get seperate compno output for &NY and &NJ. So i tried with below code
/*Table WORK.AGEGE7 created, with 477 rows and 2 columns.*/
proc sql ;
create table ageGE7 as
select var1,age from outf
where age > 7 ;
quit;

/*Table WORK.AGEGE7 created, with 410 rows and 2 columns.*/
proc sql ;
create table ageGE7NY as
select Var1,age from outf
where age > 7 and compno in (&NY);
quit;

/*Table WORK.AGEGE7 created, with 67 rows and 2 columns.*/
proc sql ;
create table ageGE7NJ as
select var1,age from outf
where age > 7 and compno in (&NJ);
quit;

In Stead of above two steps , i tried to combine in a single step, but am getting an incorrect output..

/*NOTE: The data set WORK.AGEGE7NY has 67 observations and 2 variables.*/
/*NOTE: The data set WORK.AGEGE7NJ has 67 observations and 2 variables.*/
data ageGE7NY ageGE7NJ;
SET ageGE7;
IF compno in "&NY" then output ageGE7NY;
else
IF compno in "&NJ" then output ageGE7NJ;
run;

Thanks in Advance.

Re: sql

sbb — Thu, 10 Mar 2011 16:57:09 GMT

Check your SAS code - it's clear that the different approaches are also using different SAS input files.

Scott Barry
SBBWorks, Inc.

Re: sql

CurtisMack — Thu, 10 Mar 2011 16:57:21 GMT

Two things I need to know to help. First what do you have in the NY and NJ macro variables? Two, how is the last datastep actually written, because those "IN" statements are not valid in a DATA step. Should the quotes in those statements actually be parentheses? Message was edited by: Curtis Mack

Re: sql

sbb — Thu, 10 Mar 2011 17:03:54 GMT

Helping the OP diagnose this particular SAS exercise is a guessing-game, what without the exact (COPY/PASTE) SAS-generated log being posted. I agree with the prior REPLY in that the use of "IN" in the IF/THEN construct does require surrounding parentheses, so the DATA step code as illustrated will not execute successfully.

Suggest some additional self-initiated desk-checking (given prior post replies), then if still unresolved re-post a reply with the SASLOG output (using COPY/PASTE) with call code revealed.

Scott Barry
SBBWorks, Inc.

Re: sql

deleted_user — Thu, 10 Mar 2011 17:04:13 GMT

Hi Mack,

For NY macro variables - having values (03,34,20,31) and NJ (01,11).
Instead of data step can we write proc sql , so we can get exact output.

Please let me know .

Thanks

Re: sql

SPR — Thu, 10 Mar 2011 17:21:02 GMT

Hello Ravenaat,

Look at your ifs in the last datastep:

IF compno in "&NY" then output ageGE7NY

should be

IF compno in &NY then output ageGE7NY

If you replace &NY by (03,34,20,31) in your code you get in "(03,34,20,31)" which is obvious error.

Sincerely,
SPR

Re: sql

CurtisMack — Thu, 10 Mar 2011 17:23:35 GMT

SPR said what I would have said

Re: sql

SPR — Thu, 10 Mar 2011 17:25:44 GMT

Not exactly!

Re: sql

deleted_user — Thu, 10 Mar 2011 17:33:56 GMT

Hi SPR,

As you mentioned i removed the quotes from the macro varible and tried with this data step;

data ageGE7NY ageGE7NJ;
set agege7;
IF COMPNO = &NYcomplist. then output ageGE7NY;
else
IF COMPNO = &NJcomplist. then output ageGE7NJ;
run;

but still am getting an error.

Re: sql

CurtisMack — Thu, 10 Mar 2011 17:36:36 GMT

You need to look at your code the ='s should be IN's

data ageGE7NY ageGE7NJ;
set agege7;
IF COMPNO in &NYcomplist. then output ageGE7NY;
else
IF COMPNO in &NJcomplist. then output ageGE7NJ;
run;

Re: sql

deleted_user — Thu, 10 Mar 2011 17:41:04 GMT

Mack , you mentioned the "IN" statements are not valid in a DATA step.

Re: sql

CurtisMack — Thu, 10 Mar 2011 17:45:41 GMT

I said "those "IN" statements are not valid " meaning the way they were written, not IN statements in general. Sorry, I could have been clearer.

Re: sql

Cynthia_sas — Thu, 10 Mar 2011 17:46:13 GMT

Hi:
As you can see, the IN is valid in an IF statement and in a WHERE statement in a DATA step program. Before you use macro variables, it is useful to have a -working- program to start with, so you know the correct syntax needed. (But note that my IN condition does NOT use quoted strings because AGE is a numeric variable. It would be WRONG to use IN with quoted strings for a numeric variable. And your originally posted code showed quotes around your macro variable -- which was an incorrect usage of IN -- since the IN conditions go inside parentheses.)

cynthia
[pre]
977 data one;
978 set sashelp.class;
979 if age in (11, 12, 13) then output;
980 run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.ONE has 10 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.06 seconds
cpu time 0.01 seconds

981
982 data two;
983 set sashelp.class;
984 where age in (11, 12, 13);
985 run;

NOTE: There were 10 observations read from the data set SASHELP.CLASS.
WHERE age in (11, 12, 13);
NOTE: The data set WORK.TWO has 10 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

986
987 data three four five;
988 set sashelp.class;
989 if age in (11, 12, 13) then output three;
990 else if age in (14, 15) then output four;
991 else output five;
992 run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.THREE has 10 observations and 5 variables.
NOTE: The data set WORK.FOUR has 8 observations and 5 variables.
NOTE: The data set WORK.FIVE has 1 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.07 seconds
cpu time 0.04 seconds
[/pre]

Re: sql

deleted_user — Thu, 10 Mar 2011 17:52:37 GMT

Thanks for all !!!