Help in proc sql code

[knveraraju91]

Dear,

I need help in my proc sql code. When i run the sql code, the output i am getting for the macro variables is

&treat1     Total
&treat2;       a1
&treat3;       b1

.  The output I need is 

&treat1     a1
&treat2;       b1
&treat3;       Total

For treat1   I need a1 value, for treat2   the  value b1, treat3 the value sould be total. 

Please suggest. In the sql code i tried trtn variable in the order variable, but i got error.

data sl;
input trt $1-5 trtn 7;
datalines;
a1    1
b1    2
Total 3
;
proc sql noprint;**added for table header;
    select strip(put(count (distinct trt), best.)) into: cnt from sl;
    select  distinct trt into :treat1 -:treat&cnt. from sl
 order by trt;
quit;
%put &treat1;
%put &treat2;
%put &treat3;

[Tom]

If you want SQL to generate data in a specify order you must tell it that by using the ORDER BY clause.  Looks like trtN is the variable you should be using to order by.  Note that there is no need to run the query twice just to get the count. SQL will count for you.

proc sql noprint;
select trt
  into :treat1- 
  from sl
  order by trtN
;
%let cnt=&sqlobs;
quit;

But it is probably a lot easier to just use a data step instead.

data _null_;
  set sl;
  call symputx(cats('treat',trtn),trt);
run;

[knveraraju91]

Hi TOM  Thank you very much for the help.    The data step code is working  for me instead of sql code.   The sql code you posted is working only if there are 3 obs. In my data set there are several obs. The sql did not work if add another obs.   I want to know if i can use distinct word in your sql code.  For the below dataset the sql posted is not working.  Please suggest

data sl;
input trt $1-5 trtn 7;
datalines;
a1    1
b1    2
Total 3
a1    1
;

proc sql noprint;
select trt
  into :treat1- 
  from sl
  order by trtN
;
quit;

%put &treat1;
%put &treat2;
%put &treat3;

[Tom]

Why does the file have multiple copies per TRT*TRTN combination? Doesn't seem like it was designed to use for this.  The data step version appears to work because it just updates the same macro variable multiple times.

You might get the SQL query to work by just adding back the DISTINCT keyword.

Also if you want to eliminate the note about ordering by a variable not selected then you can also pull the TRTN variable and just stuff it into some other macro variable that you can ignore.

proc sql noprint;
select distinct trt,trn
  into :treat1- 
     , :dummy 
  from sl
  order by trtN
;
%let cnt=&sqlobs;
quit;

Another difference between the two is that the SQL method is just numbering the macro variables in the order they are generated. But the data step is using the value of TRTN to generate the macro variable name.  So if your values of TRTN were 1,2,99 instead of 1,2,3 then the two methods would generate different macro variable names.