BookmarkSubscribeRSS Feed
mnjtrana
Pyrite | Level 9

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:

 


Cheers from India!

Manjeet
6 REPLIES 6
Reeza
Super User

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

PeterClemmensen
Tourmaline | Level 20

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
Pyrite | Level 9

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


Cheers from India!

Manjeet
Shmuel
Garnet | Level 18

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
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Tom
Super User Tom
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1221 views
  • 0 likes
  • 6 in conversation