hi,
I am in limbo, trying to find an option which can read all the values of one column from a dataset and write all the values in a macro variable, they should be in different lines:
HAVE: dataset named have with 5 records as below and column name addess as:
1231, Gurgaon, INDIA
142, TEXAS, US
132, Amsterdam, Netherland
011, Zxurich, Switzerland
123, Taj Mahal, Delhi
Need:
Macro variable named - ALLE having value as:
%put &ALLE;
output of %put should be:
1231, Gurgaon, INDIA
142, TEXAS, US
132, Amsterdam, Netherland
011, Zxurich, Switzerland
123, Taj Mahal, Delhi
Is it possible, thanks in advance:
That seems like a weird functionality. Why not build macros that do a print?
I dont understand this. You want to write the values of just one column to a macro variable, but in your desires output from %PUT your have several columns? 🙂
I want to put all the values of one column- address for all the observations in one macro variable. the need is to have all these values in one macro variable(ALLE).
Do you mean a program like:
data _NULL_;
length ALLE $1000;
retain ALLE;
infile datalines;
input address $;
if _N_=1 then ALLE = address;
else
if substr(address,1,3)= 'ZZZ' then
call symput('ALLE',strip allE);
else ALLE = catx(',', ALLE, address); /* you may change the delimiter from comma , to any other character */
/* like: catx(';' , ALLE, address) - using semicolon ; as delimiter */
datalines;
1231, Gurgaon, INDIA
142, TEXAS, US
132, Amsterdam, Netherland
011, Zxurich, Switzerland
123, Taj Mahal, Delhi
ZZZ; /* line added to identify end of file */
run;
%PUT ALLE = &alle;
Let SAS do all the work. Use proc transpose:
proc transpose data=have (keep=address) out=need;
var address;
run;
data _null_;
set need;
call symput('ALLE',catx('!',of col:));
run;
%put &=alle;
The CATX function separates each address by an "!". Ordinarily a "," is used, but that symbol is a part of the address value.
That is what the SEPARATED BY keyword in the INTO keyword in PROC SQL does. You do need to think about what you want to use to separate the values. Your output made it look like you want line breaks, but that concept doesn't really exist in a string value so you will need to pick a separator string based on how you plan to use the macro variable. Make sure to pick a character that cannot be in any particular address value. Also make sure that your total string length will fit in the 64K maximum size of a macro variable.
proc sql noprint ;
select address
into :ALLE separated by '|'
from have
;
quit;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.