topic Re: Import all rows in one sas macro variable in SAS Programming

Import all rows in one sas macro variable

mnjtrana — Wed, 28 Dec 2016 06:26:04 GMT

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:

Re: Import all rows in one sas macro variable

Reeza — Wed, 28 Dec 2016 07:18:02 GMT

That seems like a weird functionality. Why not build macros that do a print?

Re: Import all rows in one sas macro variable

PeterClemmensen — Wed, 28 Dec 2016 08:14:03 GMT

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? 🙂

Re: Import all rows in one sas macro variable

mnjtrana — Wed, 28 Dec 2016 08:16:38 GMT

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).

Re: Import all rows in one sas macro variable

Shmuel — Wed, 28 Dec 2016 11:14:56 GMT

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;

Re: Import all rows in one sas macro variable

mkeintz — Wed, 28 Dec 2016 13:23:07 GMT

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.

Re: Import all rows in one sas macro variable

Tom — Wed, 28 Dec 2016 16:19:33 GMT

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;