Import all rows in one sas macro variable

[mnjtrana]

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:

[Reeza]

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

[PeterClemmensen]

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

[mnjtrana]

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

[Shmuel]

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;

[mkeintz]

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.

[Tom]

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;