BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Aexor
Lapis Lazuli | Level 10

Hi All,

 

I have a macro variable having values  like below seprated by "," 

%let x =%nrstr(f588c556-15c4-4d59-b9df-88b2c655a950,06d54597-fc35-459b-bc5e-          1075e9643a1d,06d54597-fc35-459b-bc5e-2137e9643a1d);

 

I want to create a SAS data set using these values 

so output should be 

 

table out

f588c556-15c4-4d59-b9df-88b2c655a950

06d54597-fc35-459b-bc5e-1075e9643a1d

06d54597-fc35-459b-bc5e-2137e9643a1d

 

I need table out so I can run query on Table out as it is required in further steps.

 

I need your help in understanding , How to created table OUT from macro variable X.

 

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
maguiremq
SAS Super FREQ

This way gets rid of those pesky blanks that occur:

 

data want;
	var = "&x.";
	do i = 1 to countw(var, ",");
		wantvar = scan(compress(var), i, ",");
		output;
	end;
	drop var i;
run;

View solution in original post

11 REPLIES 11
FreelanceReinh
Jade | Level 19

Hi @Aexor,

 

You can put the macro variable reference &x into double quotes and then work with it as you would with any other constant string, i.e., use the COUNTW and SCAN function to count and extract the comma-separated substrings:

data out;
length c $36;
do _n_=1 to countw("&x", ',');
  c=scan("&x", _n_, ',');
  output;
end;
run;

Make sure that the length of the character variable (named c in the example above) is sufficient to accommodate the longest substring.

 

However, I would ask how these strings -- separated by "," -- got into a macro variable in the first place. In many cases it should be possible to get the strings into a SAS dataset directly from their source, without using macro variables for intermediate storage.

Aexor
Lapis Lazuli | Level 10
Thank you. Also I have one doubt. will this &x will this be able to store around 2000 observation values , like here we have 3 observation only separated by comma

%let x =%nrstr(f588c556-15c4-4d59-b9df-88b2c655a950,06d54597-fc35-459b-bc5e- 1075e9643a1d,06d54597-fc35-459b-bc5e-2137e9643a1d);
Tom
Super User Tom
Super User

To your question about the limits of a macro variable:
A macro variable can contain up to 64K bytes.  But a data step character variable can only contain up to 32K bytes.

 

Your items seem to be of length 36 bytes.  So with a one byte delimiter you could store 1,771 of them.

1698  %put %length(f588c556-15c4-4d59-b9df-88b2c655a950);
36
1699  %put %eval(64*1024/37);
1771

 

Did you really intend to type out 64K characters to create a macro variable?  Why not just type them as data instead?

data want;
  input code $36.;
cards;
f588c556-15c4-4d59-b9df-88b2c655a950
06d54597-fc35-459b-bc5e-1075e9643a1d
06d54597-fc35-459b-bc5e-2137e9643a1d
;

If you are not typing them into the code then explain where the list of codes is coming from.  It would probably be a lot easier to just leave the code in data rather than try to stuff them into a macro variable (or even multiple macro variables).

 

FreelanceReinh
Jade | Level 19

@Aexor wrote:
... I have one doubt. will this &x will this be able to store around 2000 observation values , like here we have 3 observation only separated by comma

%let x =%nrstr(f588c556-15c4-4d59-b9df-88b2c655a950,06d54597-fc35-459b-bc5e- 1075e9643a1d,06d54597-fc35-459b-bc5e-2137e9643a1d);

If each of those "observation values" has a length of 36 characters like your example strings, then you can store up to 1771 of them, separated by commas, in a single macro variable. Adding one more would exceed the maximum length of a macro variable value, which is 65,534 characters: 1772*(36+1)-1=65563. This limitation is one of the reasons why I questioned the need to store these strings in a macro variable.

 

@RW9: Good to see you again. :-)

maguiremq
SAS Super FREQ

This way gets rid of those pesky blanks that occur:

 

data want;
	var = "&x.";
	do i = 1 to countw(var, ",");
		wantvar = scan(compress(var), i, ",");
		output;
	end;
	drop var i;
run;
Aexor
Lapis Lazuli | Level 10
Thank you. This worked. Just have doubt about limitation of number of observation can be stored in X macro variable using %let .
here in example I have mentioned only 3 observation. I need to store around 2000 to 3000 observation in this. will this work ?
Kurt_Bremser
Super User

Macro variables are not meant to store data, but pieces of code. They are limited to 64k characters, so you cannot store 2000 UUIDs (each 36 characters) in one.

How do those 2000 UUIDs arrive in your SAS environment?

Aexor
Lapis Lazuli | Level 10
Thank for the info. Getting UUIDS from a different macro variable .
Kurt_Bremser
Super User

A macro variable is already part of SAS. How does the data initially arrive in your SAS environment?

And if the macro variable is filled from data out of a dataset, you can use the same step to create the code (statements are not limited in length), or use that extract in joins/hashes.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Not tested, but something like:

%let x =%nrstr(f588c556-15c4-4d59-b9df-88b2c655a950,06d54597-fc35-459b-bc5e-1075e9643a1d,06d54597-fc35-459b-bc5e-2137e9643a1d);

data want;
  do i =1 to countw("&x.",",");   
    result=scan("&x.",i,",");
    output;
  end;
run;

Essentially count how many comma separated items, then substr each by delimiter comma and output.

 

To add, macro variables are not th eplace to store data, there are many ways to store it, json, xml, datasets etc.

Chabane_Haddad
Calcite | Level 5
%let x =%nrstr(f588c556-15c4-4d59-b9df-88b2c655a950,06d54597-fc35-459b-bc5e-1075e9643a1d,06d54597-fc35-459b-bc5e-2137e9643a1d);

data test (keep =var1-var3);
string=symget('x');
count=countw(string, ',');
array myvars (3) $ 100 var1-var3;
do i=1 to dim(myvars);
myvars(i)=scan(string, i, ',');
end;
run;

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 11 replies
  • 1951 views
  • 6 likes
  • 7 in conversation