Reading from macro variable to create dataset

[forumsguy]

Hi All,

My requirement is to create new dataset based on two macro variables. There are two macro variables present one to have variable list and another to have format of the variables.. So

Code : %PUT OUTPUT_VARIABLE_NAME IS &OUTPUT_VARIABLE;

Log : OUTPUT_VARIABLE_NAME IS ACCOUNT_ID  DEFAULT_MONTH  OUTCOME  OUTCOME_MONTH DEFAULT_CURRENT_BALANCE

OUTCOME_CURRENT_BALANCE

Code : %PUT OUTPUT_VARIABLE_FORMAT is &OUTPUT_VARIABLE_FORMAT;

OUTPUT_VARIABLE_FORMAT IS $20. $6. $20. $6. best12. best12.

So my requirement is something like this

DATA NEW(Keep=&OUTPUT_VARIABLE_NAME);

FORMAT &OUTPUT_VARIABLE_NAME &OUTPUT_VARIABLE_FORMAT);

RUN;

Please suggest any solution.

[data_null__]

That syntax is not going to work in a FORMAT statement but it will work in an INPUT statement.

%let OUTPUT_VARIABLE_NAME = ACCOUNT_ID  DEFAULT_MONTH  OUTCOME  OUTCOME_MONTH DEFAULT_CURRENT_BALANCE;
%PUT OUTPUT_VARIABLE_NAME IS &OUTPUT_VARIABLE_NAME;
%let OUTPUT_VARIABLE_FORMAT = $20. $6. $20. $6. best12. best12.;
%PUT OUTPUT_VARIABLE_FORMAT is &OUTPUT_VARIABLE_FORMAT;

DATA NEW(Keep=&OUTPUT_VARIABLE_NAME);
   stop;
   input (&OUTPUT_VARIABLE_NAME) (&OUTPUT_VARIABLE_FORMAT);
   cards;
  
;;;;
   run;
proc contents varnum;
   run;

[WaltSmith]

Here's a macro and a sample of how to use it that should help:

options mprint nosource;
%macro Make_Fmt_Stmts( varlist, fmtlist );
   %local done i;
   %let done = notyet;
   %do %until(&done=DONE);
      %let i = %eval( &i +1 );
      %let var = %qscan( &varlist, &i, %str( ));
      %let fmt = %qscan( &fmtlist, &i, %str( ));
      %if ( %length(&var) = 0 ) %then %let done = DONE;
      %else  format &var &fmt %str(;);
   %end;
%mend;
data new;
   set sashelp.class;
   %Make_Fmt_Stmts( name sex age height weight, $9. $1. 3. 6.2 6.2 )
run;
proc contents data=new;
proc print data=new;
run;

[Tom]

Just a comment prompted by your example formats.  There is very little value from adding $nn. formats to character variables. And there is potential for a lot of confusion down the road if you merge those datasets with others where the length of the variables are different.  You could end up with the situation where the format is different length than the variable.  So it could result it values not displaying or being used in statistical procs because of truncation.

[WaltSmith]

you're kidding me right?

In an example designed to show formatting a list of variables with a list of formats you're fussing about which formats are used? I suppose I could have used a custom format, undefined and unaddressed in the request - or some other character format native to SAS but certainly not the type of format that was expressly specified in the request!!??

[Tom]

You are right.

But it is a serious problem.  If you run proc freq on a 10 character variable that has accidentally had $6. format attached to it you could end up with fewer categories than you should.  Plus there was just another issue today highlighting that PROC SQL is lacks the ability to clear attached formats. 

[WaltSmith]

A far more relevant criticism would be that I use two macro variables that were not explicitly declared to be local and thereby have the potential to influence the parent environment. I should have added the following line:

%local var fmt;