How to read a macro variable value and convert it in SAS data set

[Aexor]

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.

[FreelanceReinh]

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]

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]

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]

---

@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 · Accepted solution]

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]

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]

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]

Thank for the info. Getting UUIDS from a different macro variable .

[Kurt_Bremser]

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]

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]

%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;