topic select into: in SAS Procedures

select into:

cathy_sas — Fri, 27 Mar 2015 16:22:43 GMT

HI All,

I have a dataset with two group, suppose A & B. But for some criteria, only one group is avaliable. i need to keep 0 for the other group on header count.

data have;

input var group

crit1 A

crit1 B

crit2 B

crit3 A

crit3 B

;

run;

i need to only with criteria 2

My code:

data temp;

set have;

if var eq 'crit2';

run;

proc sql;

select count(distinct var) into :grp1 -:grp2 separated by ' '

group by group

;

quit;

later in report i am using those header count macro's in report header count

want :

categ A B

N=&grp1(0) N=&grp2(1)

crit2 0 1

but getting:

categ A B

N=&grp1(1) N=&grp2 (&grp2 (warning on log &trt2 is not resolved)

crit2 0 1

THanks

Cathy

Re: select into:

PGStats — Fri, 27 Mar 2015 17:29:44 GMT

I cannot reproduce your results. I get ERROR messages as soon as the SAS compiler hits the syntaxically incorrect " :grp1 -:grp2 separated by ' ' ". Please post code that runs OR the ERROR messages that the code generates.

Re: select into:

cathy_sas — Fri, 27 Mar 2015 17:40:22 GMT

Hi,

I am also getting a warning while creating macro variable using proc sql.

Find the warning message below:

SYMBOLGEN: Macro variable GRP1 resolves to 12

WARNING: Apparent symbolic reference GRP2 not resolved.

%put &grp1 &grp2;

1 &grp2

In my above example. A is group1 & B is group2. For the criteria2 , i have only group2 , no group1. so i need to show on my header count: group1(N=0) & group2(N=1)

Thanks

Cathy

Re: select into:

Patrick — Sat, 28 Mar 2015 00:19:35 GMT

As answered, your code is not working. Below some working example. Not sure if this is what you're after.

As this is about getting the headers in reports for categories which are not in your data and depending on the Proc you're using an alternative approach could be to use "classdata" as documented here Base SAS(R) 9.4 Procedures Guide, Third Edition

I'm that there are other ways for other Proc's to create similar results.

data have;

input var $ group $;

datalines;

crit1 A

crit1 B

crit2 B

crit3 A

crit3 B

;

run;

%let grp1=0;

%let grp2=0;

proc sql;

select count(distinct var) into :grp1 -:grp2

from have

where var eq 'crit2'

group by group

;

quit;

%put &=grp1;

%put &=grp2;

Re: select into:

Jagadishkatam — Sat, 28 Mar 2015 00:52:31 GMT

Please try , i did this by creating the dummy dataset

data have;

input var$ group$;

cards;

crit1 A

crit1 B

crit2 B

crit3 A

crit3 B

;

proc sort data=have out=dummy nodupkey;

by var;

run;

data dummy_;

set dummy(keep=var);

do group='A','B';

output;

end;

run;

data want;

merge have(in=a) dummy_;

by var group;

if a then cnt=1;

else cnt=0;

run;

proc sql;

select cnt into :grp1 -:grp2

from want where var='crit2';

quit;

%put &grp1 &grp2;

Thanks,

jag