- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thanks Linlin..
Can you help me to get same result with proc sql
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thank you Tom! I did that while I was half asleep.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thank........U Tom its working
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thank.......U Linlin its working