MACROS

[rawindar]

how can we write a code

i need to create a macro variables with each observation into one macro variable.Can you help me to write a code using PROC SQL

eno ename sal

1 a 1000

2 b 2000

3 c 3000

..............

i need to assign first observation in to m1

                        2nd obs into m2

                        3rd obs into m3

................................................

Thanks in advance

regards

Rawindarreddy

[jcbell]

You can concatenate the variables and then assign them to a macro variable:

Data _null_;

set work.foo;

call symput (compress('m' || _n_),cat(eno,ename,sal));

run;

[Linlin]

data foo;

input eno ename $ sal;

cards;

1 a 1000

2 b 2000

3 c 3000

;

data _null_;

set foo;

/*if you need a space or something between the variables*/

call symputx(cats('m',strip(_n_)),catx(' ',eno,ename,sal));

/*if you don't need anything between the variables*/

call symputx(cats('mm',strip(_n_)),cats(eno,ename,sal));

run;

%put _user_;

Linlin

[rawindar]

Thanks Linlin..

Can you help me to get same result with  proc sql

[Linlin · Accepted solution]

how about:

data foo;

input eno ename $ sal;

cards;

1 a 1000

2 b 2000

3 c 3000

;

proc transpose data=foo out=temp;

var _all_;

run;

proc sql noprint;

  select col1

      , col2

      , col3

  into  :m1 separated by ','

      ,:m2 separated by ','

      ,:m3 separated by ','

  from temp

  ;

quit;

%put _user_;

Updated after Tom's comments.

[Tom]

The syntax for creating multiple macro variables in a single SQL query is a little confusing.  You need to list values to select before the INTO and the list of targets after.

proc sql noprint;
  select col1 
       , col2
       , col3
  into :m1 separated by ','
     , :m2 separated by ','
     , :m3 separated by ','
  from temp
  ;
quit;

[Linlin]

Thank you Tom! I did that while I was half asleep.

[Haikuo]

Late for the party, but less hard coding:

data foo;

input eno ename $ sal;

cards;

1 a 1000

2 b 2000

3 c 3000

;

proc sql;

select name into :name separated by ','

from dictionary.columns

where libname='WORK' and memname='FOO';

select count(*) into : ct from foo;

%let ct=&ct;

select catx(' ',&name) into : m1- :m&ct    from foo;

quit;

Regards,

Haikuo

[rawindar]

Thank........U Tom its working

[rawindar]

Thank.......U  Linlin its working