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
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.
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;
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
Thanks Linlin..
Can you help me to get same result with proc sql
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.
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;
Thank you Tom! I did that while I was half asleep.
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
Thank........U Tom its working
Thank.......U Linlin its working
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.