How to create macro variables using proc sql

[knveraraju91]

Dear,

I need to create macro variables for the output values produced using proc sql;

my code:

proc sql;
create table one as
select count(distinct usubjid) as NS,TRT1
from data1
group by TRT1;
quit;

output produced by sql code;

NS     TRT1

50        A

60        B

40        C

150       Total

How to create 4 macro variables equal to A,B,C, and Total, so I can use these later in data step. Please help

Thanks

[Reeza]

Don't use SQL use a data step with CALL SYMPUTX.  

Proc SQL doesn't have a method for dynamically assigning macro variable names. 

[knveraraju91]

I am sorry that if I didnot explain properly.

I am using PROC sql to get the values in my program. I also need to assign the values into a macro variables using INTO: so that I can use the values later in the step. Thank you

[Jagadishkatam]

try the below step

proc sql;
select count(distinct usubjid) into: TRT1-
from data1
group by TRT1 order by trt1;
quit;

%put &TRT1 &TRT2 &TRT3 &TRT4;

%let A=&TRT1;
%let B=&TRT2;
%let C=&TRT3;
%let TOTAL=&TRT4;


This will create 4 macro variables like TRT1 ,TRT2, TRT3 and TRT4 with counts and macro variable names could be changed to as provided.

[Reeza]

I don't think you can create macro variables and a data set at the same time. Since you need another step use a data step. 

I understood your question as you want to create 4 macro variables called A, B, C & Total which are one column and the values to be from the second column. This can't be done in a single SQL query either, or at least simply. 

If you just want all 4 numerical values into 4 different macro variables named somerging like var1-var4 or a single macro variable then use the examples in the documentation. However, given your experience on the forum I assumed you were asking a more complex question. 

[Jagadishkatam]

I agree with @Reeza to get the macro variable names with treatment names then the best is to use call symputx.
use the dataset you developed by proc sql which has the count and treatment variable like below

NS TRT1
50 A
60 B
40 C
150 Total

then, try

data one;
input NS TRT1$13.;
cards;
50 A
60 B
40 C
150 Total
;

data _null_;
set one;
call symputx(trt1,ns);
run;

%put &a &b &c &total;

[rogerjdeangelis]

Here is a related solution

/* T008040 Placing counts in proc report column headers

inspired by
How to create macro variables using proc sql
https://goo.gl/EtsSFo
https://communities.sas.com/t5/Base-SAS-Programming/How-to-create-macro-variables-using-proc-sql/m-p/326471

Suppose you want to produce a report with a header
that contains 'treatment name and counts

HAVE

Up to 40 obs from SexGov total obs=50

Obs    SEX       GOV  IDS   SERIAL    PAY

  1    Female    FBI   13    25439   3463
  2    Female    FBI   14   629949  28614
  3    Female    FBI   17   851828  27102
  4    Female    FBI   20    66992  19882
  5    Female    FBI   23   659046  34665
  6    Female    FBI   25   248322  17126
....
 37    Male      CIA   17   786544  47492
 38    Male      CIA   21   716109  27873
 39    Male      CIA   22   731075  22004
 40    Male      CIA   23   933118  11092

WANT

               Female          Male
    GOV        N = 21        N = 29

    CIA             5             9
    FBI             6            10
    IRS            10            10


SOLUTION WORKING CODE
=====================

   resolve(catx(' ','%let',sex,'=%str(',sex,'# N =',put(count(*),2. -l),');'))


FULL SOLUTION
=============

   * create sample data;
   Data  SexGov;
   Do Sex='Female','Male';
     Do Gov='FBI','CIA','IRS';
       Do Ids=10 to 25;
           Serial=Int(1E6*Uniform(5739));
           Pay=Int(50000*Uniform(57343));
           If Uniform(5643) < .5 Then Output;
         End;
       End;
     End;
   Run;

   * create macro variable Male with  "Male # N=29";
   proc sql;
      create
         table hdr as
      select
         resolve(catx(' ','%let',sex,'=%str(',sex,'# N =',put(count(*),2. -l),');'))
        ,*
      from sexgov group by sex;quit;

   %put &=female;
   %put &=male;

   /*
   FEMALE= Female # N = 21
   MALE  = Male # N = 29
   */

   /* Put counts in Rectangular array */
   Ods Exclude All;
   Ods Output Observed=hdr(Rename=Label=Gov
       drop=sum where=(gov ne 'Sum'));
   Proc Corresp Data=SexGov Observed dim=1;
      Table Gov, Sex;
   Run;
   Ods Select All;

   * print report note the macro variables;
   proc report data=hdr nowd missing split='#' headskip;
     cols Gov Female Male;
     define Female /display "&female" width=12;
     define Male   /display "&male" width=12;
   run;quit;


              Female          Male
   GOV        N = 21        N = 29

   CIA             5             9
   FBI             6            10
   IRS            10            10