topic Re: SAS: create a string to use as a macro in SAS Programming

SAS: create a string to use as a macro

Pooja2 — Mon, 05 Aug 2019 15:09:07 GMT

data have;

infile datalines missover;

input IDnumber1 $ IDnumber2 $ IDnumber3 $;

datalines;

2477 sdsd dfdf

2431

2456 dfdf ghgh

2412 erer ghgh

;

run;

I want to create a variable for each obs like this

newvar for row 1: '2477','sdsd', 'dfdf'

newvar for row 2: '2431'

newvar for row 3: '2456','dfdf','ghgh'

newvar for row 4: '2412','erer','ghgh'

I want to create this to use them as a macro in a where clause.

Thanks,

Pooja

Re: SAS: create a string to use as a macro

PaigeMiller — Mon, 05 Aug 2019 15:16:04 GMT

@Pooja2 wrote:

data have;

infile datalines missover;

input IDnumber1 $ IDnumber2 $ IDnumber3 $;

datalines;

2477 sdsd dfdf

2431

2456 dfdf ghgh

2412 erer ghgh

;

run;

I want to create a variable for each obs like this

newvar for row 1: '2477','sdsd', 'dfdf'

newvar for row 2: '2431'

newvar for row 3: '2456','dfdf','ghgh'

newvar for row 4: '2412','erer','ghgh'

I want to create this to use them as a macro in a where clause.

Do you mean you want to use them as a MACRO VARIABLE? (This is not the same as using them as a MACRO)

data _null_;
infile datalines missover;
input IDnumber1 $ IDnumber2 $ IDnumber3 $;
call symputx('mvar'||left(_n_),catx(',',quote(trim(idnumber1)),
    quote(trim(idnumber2)),quote(trim(idnumber3))));
datalines;
2477 sdsd dfdf
2431
2456 dfdf ghgh
2412 erer ghgh
;

run;

This creates macro variables &MVAR1 &MVAR2 &MVAR3 and &MVAR4, one from each row of the input data.

Re: SAS: create a string to use as a macro

Tom — Mon, 05 Aug 2019 15:15:29 GMT

You can use QUOTE() to add quotes. And CATX() to add the commas.

data want;
   set have ;
   length string $200;
   string=catx(',',quote(trim(idnumber1)),quote(trim(idnumber2)),quote(trim(idnumber3)));
run;

Do you need single quotes or are double quotes good enough? For example you might be using the value to generate code that is passed into a remote database that requires single quotes. Or the values might contain & or % characters that would trigger the macro processor if using inside of double quotes.

quote(trim(idnumber1),"'")

Re: SAS: create a string to use as a macro

ballardw — Mon, 05 Aug 2019 15:25:48 GMT

A considerable percent of the time when someone attempts to stuff an entire dataset into macro variables it indicates that they are about to abuse the macro system or are about to attempt something that probably does not belong in macros.

So, how exactly are you using those macro variables after they are created? If ALL of those are going into a single Where clause then almost certainly macro variables are not needed.

If you already have values in a data set often some form of join is appropriate to select records.

Re: SAS: create a string to use as a macro

Jagadishkatam — Mon, 05 Aug 2019 15:34:22 GMT

alternative way with proc sql to create the macro variables

data have;
infile datalines missover;
input IDnumber1 $ IDnumber2 $ IDnumber3 $;
newvar=cats(quote(compress(IDnumber1)),',',quote(compress(IDnumber2)),',',quote(compress(IDnumber3)));
datalines;
2477 sdsd dfdf
2431
2456 dfdf ghgh
2412 erer ghgh
;
run;

proc sql;
select newvar into :mvar1 - : mvar&sysmaxlong from have;
quit;

%put &mvar1 &mvar2  &mvar3  &mvar4;

Re: SAS: create a string to use as a macro

andreas_lds — Mon, 05 Aug 2019 15:38:49 GMT

Just want to underline what @ballardw already said: storing data in macro variables is almost always a bad idea or a lack of knowledge.

Re: SAS: create a string to use as a macro

Pooja2 — Mon, 05 Aug 2019 17:25:51 GMT

Thanks Tom for your response.

it is giving me "2412","erer","ghgh" for nonmissing values but if I have any missing value it is giving me "2431"," "," " if I have missing values. If I have missing values then I just want "2431". so that my data pull does not give me any blank cells when I use this in my where clause.

Thanks,

I have a dataset where I have different customers cust 1, cust 2, ...custn in each rows and then their corresponding ids as id1, id2, id3. Sometimes more than 1 id is missing but at least one id will be populated. I have 150 rows. I have to create a summary for each customer (if there id in id1,id2, and id3) for example - how many shirts they have bought. so if I have a variable for id then I can use that in a where clause as

PROC SQL ; SELECT COUNT(*) INTO :NUMROWS FROM want; QUIT;

PROC DELETE DATA=TOGETHER;

%MACRO SHIRT;

%DO I=1 %TO &NUMROWS;

DATA CHECK;SET WANT; IF _N_=&I;RUN;

PROC SQL;

SELECT CUST AS CUST, ID AS ID INTO :CUST, :ID FROM CHECK;

QUIT;

PROC SQL;

CREATE TABLE CLMS AS

SELECT *

FROM OTHERDATA

WHERE A.ID IN ("&ID.")

AND A.CUSTOMER IN ("&CUST.")

QUIT;

%END;

Thanks,

Re: SAS: create a string to use as a macro

Pooja2 — Mon, 05 Aug 2019 17:28:46 GMT

Thanks Ballardw. I am still learning and I am aware I need to improve. I explained my problem in detail in the post. I hope you can give me your suggestion based on my problem.

Re: SAS: create a string to use as a macro

Kurt_Bremser — Mon, 05 Aug 2019 17:43:14 GMT

So you have a badly structured dataset to begin with. Data (sale items or similar) stored as column names.

Start with transposing to a long format, and what you want to analyze becomes dead simple.

@Pooja2 wrote:

Thanks Tom for your response.

it is giving me "2412","erer","ghgh" for nonmissing values but if I have any missing value it is giving me "2431"," "," " if I have missing values. If I have missing values then I just want "2431". so that my data pull does not give me any blank cells when I use this in my where clause.

Thanks,

I have a dataset where I have different customers cust 1, cust 2, ...custn in each rows and then their corresponding ids as id1, id2, id3. Sometimes more than 1 id is missing but at least one id will be populated. I have 150 rows. I have to create a summary for each customer (if there id in id1,id2, and id3) for example - how many shirts they have bought. so if I have a variable for id then I can use that in a where clause as

PROC SQL ; SELECT COUNT(*) INTO :NUMROWS FROM want; QUIT;

　

PROC DELETE DATA=TOGETHER;

%MACRO SHIRT;

%DO I=1 %TO &NUMROWS;

DATA CHECK;SET WANT; IF _N_=&I;RUN;

PROC SQL;

SELECT CUST AS CUST, ID AS ID INTO :CUST, :ID FROM CHECK;

QUIT;

PROC SQL;

CREATE TABLE CLMS AS

SELECT *

FROM OTHERDATA

WHERE A.ID IN ("&ID.")

AND A.CUSTOMER IN ("&CUST.")

QUIT;

%END;

Thanks,

Re: SAS: create a string to use as a macro

Pooja2 — Mon, 05 Aug 2019 17:54:48 GMT

I have unique cust in each row. It is a clean data.

Re: SAS: create a string to use as a macro

Tom — Mon, 05 Aug 2019 17:56:01 GMT

It shouldn't matter if the list includes the blanks (or duplicate values either) if you are just using it in a WHERE clause.

If you want to ignore blank values (why would you have blank values in the column being queried?) you can add another criteria.

%let list="2431"," "," " ;
....
  where item in (&list) 
    and not missing(item)
....

Re: SAS: create a string to use as a macro

Astounding — Mon, 05 Aug 2019 19:08:18 GMT

Notice how many times you are hitting your claims data. If you have 1,000 claims to look up, your plan is to hit the claims data 1,000 times. Surely there must be another way.

Here's the approach I would try. The syntax is not checked, as you have the data, but the approach should extract all the claims of interest with just one pass through the data:

proc sql;
create table want as select * from otherdata
where catx(cust, ',' , id) in select (catx(cust, ',' , id) from check) ;
quit;

Re: SAS: create a string to use as a macro

ballardw — Mon, 05 Aug 2019 19:42:19 GMT

@Pooja2 wrote:

Thanks Ballardw. I am still learning and I am aware I need to improve. I explained my problem in detail in the post. I hope you can give me your suggestion based on my problem.

Provide examples of both input data sets and the desired result(s) is usually a best practice for any question.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Re: SAS: create a string to use as a macro

Kurt_Bremser — Tue, 06 Aug 2019 05:42:54 GMT

@Pooja2 wrote:

I have unique cust in each row. It is a clean data.

Every time I see something like

input IDnumber1 $ IDnumber2 $ IDnumber3 $;

the back of my head screams "Design problem! Un-transpose!". And that's what will happen in 99% of the time.

So either you have a table design problem, or you did not give us a useful example of your dataset.

And what will you be doing with the resulting macro variable, anyway? See Maxim 43. Because this will determine the (best) method to use, and I have a strong inkling that it won't be a large macro variable.