Building and appending a macro variable while on a datastep

[DucatiJong]

Hello,

I have an approach which uses multiple column values from a dataset (MyData) to create a macro var (MyLIst)  which i use as a run statement on later step. This approach works but I was hoping for a more efficient approach.

Is there a way that i can build and append the macro var (MyList) within the PrepData step and bypass the proc sql altogether? One potential issue with my approach is that as i am storing the components of MyList on the column Stmt. As Stmt is a column, i needed to define its length. This can get very large and so require constant monitoring. An illustration of my query below.  

data PrepData;
set MyData (keep=ColumnA ColumnB);

length Stmt $300;
Stmt = cats(ColumnA,ColumnB);
run;

proc sql;
select Stmt into:MyList separated by ' '
from PrepData;
quit;

Thanks in advance.

[PeterClemmensen]

You can do something like this directly in the data step. I just made some data up for demonstration

data MyData;
input ColumnA $ ColumnB $;
datalines;
one two
three four
five six
;

data _null_;
    set MyData (keep=ColumnA ColumnB) end=lr;
    length Stmt $300 StmtCon $500;
    retain StmtCon;
    Stmt = cats(ColumnA,ColumnB);
    StmtCon=catx(' ', StmtCon, Stmt);
    if lr then call symputx('MyList', StmtCon);
run;

%put &MyList.;

[DucatiJong]

So i do still need to go the path of using a variable. I was hoping to directly go straight into a macro variable so i don't have to worry about variable length.

[DucatiJong]

Thanks for your reply. I had wanted to skip writing to a variable as my resulting macro can get quite long - actual list below containing the append of 3 rows. 

if missing(Customer_ID) then do; Customer_ID= &CUSTOMERID; X_D_CUSTOMERID= 1; end;

if missing(Customer_Name) then do; Customer_Name= "&CUSTOMERNAME"; X_D_CUSTOMERNAME= 1; end;

if missing(Customer_Group_ID) then do; Customer_Group_ID= &CUSTOMERGROUPID; X_D_CUSTOMERGROUPID= 1; end;

My intention here is to allow for the setting of a default value for selected columns when its missing, and include a default set flag.

[SASKiwi]

The maximum length of a character or macro variable in SAS is 32,767 bytes / characters: https://documentation.sas.com/?docsetId=basess&docsetTarget=n1cruyh1wg40v9n1ddf1lkrcs2j0.htm&docsetV...

What is the maximum length of the string you are trying to store?

[DucatiJong]

I was trying to bypass writing to a variable altogether as each of the statement is quite long - if a variable value is missing then do, assign default value, assign exception flag.

[ballardw]

---

@DucatiJong wrote:

Hello,

I have an approach which uses multiple column values from a dataset (MyData) to create a macro var (MyLIst)  which i use as a run statement on later step. This approach works but I was hoping for a more efficient approach.

Is there a way that i can build and append the macro var (MyList) within the PrepData step and bypass the proc sql altogether? One potential issue with my approach is that as i am storing the components of MyList on the column Stmt. As Stmt is a column, i needed to define its length. This can get very large and so require constant monitoring. An illustration of my query below.  

data PrepData;
set MyData (keep=ColumnA ColumnB);

length Stmt $300;
Stmt = cats(ColumnA,ColumnB);
run;

proc sql;
select Stmt into:MyList separated by ' '
from PrepData;
quit;

Thanks in advance.

---

If you have SAS syntax in variables in a data step perhaps you are looking for something like CALL EXECUTE.

A pretty trivial example where each value of a variable contains a single valid SAS statement.

data work.syntax;
   length x $ 100.;
   x = "Proc print data=sashelp.class;";output;
   x = "var name age height;"; output;
   x = "format age z3.;"; output;
   x = "run;"; output;
run;


data _null_;
   set work.syntax;
   call execute(x);
run;

If your actual data, of which you have not provided any example, contain pieces of statements you can use character functions to create valid syntax portions and use call execute to send them to the processor.

Call execute stacks up (hopefully valid) syntax elements and executes AFTER the data step calling them ends.

Search this forum for more complicated examples of Call Execute.

[DucatiJong]

Thanks for your reply. I had wanted to skip writing to a variable as my resulting macro can get quite long - actual list below containing the append of 3 rows. 

if missing(Customer_ID) then do; Customer_ID= &CUSTOMERID; X_D_CUSTOMERID= 1; end;

if missing(Customer_Name) then do; Customer_Name= "&CUSTOMERNAME"; X_D_CUSTOMERNAME= 1; end;

if missing(Customer_Group_ID) then do; Customer_Group_ID= &CUSTOMERGROUPID; X_D_CUSTOMERGROUPID= 1; end;

My intention here is to allow for the setting of a default value for selected columns when its missing, and include a default set flag.

[ballardw]

Data. Example data is good.

Expected generated code, as that seems to be what you are attempting, you haven't shown any actual desired result for a given example input.

Where do &CUSTOMERID, &CUSTOMERNAME and &CUSTOMERGROUPID come from? If you are using the same value for each missing then why is there a macro variable involved at all? That implies to me that there is something else going on that you are not explaining, such as pulling a specific value from a data set for some condition not mentioned to select a "default".

You don't show anything that might be creating those. (Remember the comment about no data )

There are lots of different ways to provide values from a dataset to another, Merge, Update, Modify, Joins to name a few. But example data might give us a better idea.

Almost anything that appears to be sticking multiple statements into macro variables is likely to be a suboptimal approach for a number of reasons such as lengths of macro variables as mentioned by @SASKiwi and @Tom plus the nature of code that can't be inspected easily (because it is in effect hidden in a macro variable).

[DucatiJong]

Here a sample with data of what i was trying to achieve. As a recap the objective was to assign a default to columns that contains missing value. The example below is a simplified version but logic remain complete. The real code contains some conditional assignments and also different treatment for numeric and character columns. I am working with quite a large list of variables i need to set default for which was why i had wanted to bypass writing to Stmt (and maintain the length) and thought to go direct to macro variable. Noted the problem of 65k length restriction raised..

data Control_Table;
length Column_Name Ref_Name Default_Value $20;
infile datalines delimiter =',';
input Column_Name $ Ref_Name $ Default_Value $;
datalines;
Customer_ID,CustID,-1
Customer_Name,CustNm,Unknown
Customer_Group_ID,GrpID,-1
;
run;

data Prep;
set Control_Table;

call symputx (Ref_Name, Default_Value);

length Stmt $1000;
Stmt = 'if missing('||strip(Column_Name)||') then do; '
||strip(Column_Name)||'= "&'||strip(Ref_Name)||'"; '
||'X_D_'||strip(Ref_Name)||'= 1; '
||'end;'
;
run;

proc sql;
select Stmt into:Default_Treament separated by ' '
from Prep;
quit;

%put %superq(Default_Treament);

data Source_Data;
length Customer_ID Customer_Name Customer_Group_ID $10;
infile datalines delimiter =',';
input Customer_ID $ Customer_Name $ Customer_Group_ID $;
datalines;
. ,CustomerA ,GRPA
B12345 ,. ,.
. ,CustomerX ,.
;
run;

data Output;
set Source_Data;
&Default_Treament.;
run;

[Tom]

I would not try to do that with a macro variable unless you are certain that the maximum number of bytes generated is less than 64K.

But if you do want to do there is no need for two steps.

proc sql noprint;
select cats(ColumnA,ColumnB)
  into :MyList separated by ' '
  from MyData 
;
quit;