topic Re: How to read a macro variable value and convert it in SAS data set in SAS Programming

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

Aexor — Wed, 15 Feb 2023 12:33:49 GMT

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.

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

FreelanceReinh — Wed, 15 Feb 2023 13:16:28 GMT

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.

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

maguiremq — Wed, 15 Feb 2023 13:28:04 GMT

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;

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

Aexor — Wed, 15 Feb 2023 15:25:05 GMT

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

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

Aexor — Wed, 15 Feb 2023 15:26:41 GMT

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 ?

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

RW9 — Wed, 15 Feb 2023 15:33:51 GMT

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.

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

Tom — Wed, 15 Feb 2023 15:59:29 GMT

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

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

FreelanceReinh — Wed, 15 Feb 2023 16:01:22 GMT

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

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

Kurt_Bremser — Wed, 15 Feb 2023 18:36:11 GMT

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?

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

Chabane_Haddad — Wed, 15 Feb 2023 19:36:31 GMT

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

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

Aexor — Wed, 22 Feb 2023 13:29:57 GMT

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

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

Kurt_Bremser — Thu, 23 Feb 2023 06:39:33 GMT

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.