select into:

[cathy_sas]

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

[PGStats]

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.

PG

[cathy_sas]

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

[Jagadishkatam]

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

[Patrick]

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;