use macro variable in put statements for file out

[Yeti]

Greetings,

I am trying to create one universal program to generate tab delimited file for our datafeeds.  I have a dataset that has all the field names, formats and the datafeed itself.  The plan is to automate the put statements in the program.

However, I am having little difficulties trying to implment this design.

I apologize in advance for the long post.  I could not find solutions in the board for what I am looking for.

Here is the sample code if you are interested to run this in your computer (just need to edit the file location)

OPTIONS nocenter PS=max LS=max NOFULLSTIMER obs=max symbolgen mprint mlogic msglevel=i compress=y missing=' ';

data LAYOUT;
INFILE DATALINES DSD;
LENGTH HEADER COLUMN_NAMES COL_FORMAT $ 25;
input HEADER $ COLUMN_NAMES $ COL_FORMAT $;
datalines;
Full Name, FULL_NAME, $25.
Date of Birth, DOB, mmddyy10.
Yearly Salary, SALARY_YEARLY, 10.2
;
run;

DATA DETAIL;
INFILE DATALINES DSD;
input FULL_NAME :$25. DOB :MMDDYY10. SALARY_YEARLY;
FORMAT DOB DDMMYY10. ;
datalines;
"JOHN DOE", 01/01/1960, 40000
"JANE DOE", 01/01/1965, 45000
"DAVE SMITH", 01/01/1970, 50000
;
run;

proc sql noprint;
select count(*) into :COL_COUNT separated by ',' from LAYOUT;
select HEADER into :HEADER separated by ',' from LAYOUT;
select COLUMN_NAMES into :COLUMN_NAMES separated by ',' from LAYOUT;
select COL_FORMAT into :COL_FORMAT separated by ',' from LAYOUT;
quit;

%PUT &COL_COUNT;
%PUT &HEADER;
%PUT &COL_FORMAT;

data array_fields (drop=cnames cname_descrps cname_formats x) ;
array cname{*} $50 cname1-cname&COL_COUNT;
array cname_descrp{*} $75 cname_descrp1-cname_descrp&COL_COUNT;
array cname_format{*} $75 cname_format1-cname_format&COL_COUNT;
cnames="&COLUMN_NAMES";
cname_descrps="&HEADER";
cname_formats="&COL_FORMAT";
do x = 1 to dim(cname);
	cname[x] = scan (cnames, x, ',');
	cname_descrp[x] = scan (cname_descrps, x, ',');
	cname_format[x] = scan (cname_formats, x, ',');
end;
run;


%macro globalize_fields;
%do x = 1 %to &COL_COUNT;
	 putheader&x putdetail&x putformat&x
%end;
%mend globalize_fields;


%MACRO CreateArrayFields;
%global %globalize_fields;

%do i = 1 %to &COL_COUNT;
	proc sql outobs=1 noprint;
		select trim(cname_descrp&i) into :putheader&i separated by '|' from array_fields;
		select trim(cname&i) into :putdetail&i separated by '|' from array_fields;;
		select trim(cname_format&i) into :putformat&i separated by '|' from array_fields;;
	quit;
%end;
%mend CreateArrayFields;

%macro put_fields_out;
tab=('09'x);
if _n_ = 1 then do;
put @1
%do x = 1 %to &COL_COUNT;
	"&putheader&x"  
        %if &x < &COL_COUNT %then %do; tab $1. %end;
%end;
;
end;
put @1 
%do x = 1 %to &COL_COUNT;
	&putdetail&x "&putformat&x" 
        %if &x < &COL_COUNT %then %do; tab $1. %end;
%end;
;
%mend put_fields_out;

%macro fileout;
data _null_;
	SET DETAIL;
	file '/sashome/ngurung/fileout.txt';
	%put_fields_out;
run;
%mend fileout;

%CreateArrayFields;
%fileout;

when I run this, I get the following error:

MLOGIC(PUT_FIELDS_OUT):  %DO loop index variable X is now 4; loop will not iterate again.
MPRINT(PUT_FIELDS_OUT):   put @1 
"Full Name" tab $1. 		  --As expected.
"Date of Birth" tab $1. 
"Yearly Salary" 
;
MPRINT(PUT_FIELDS_OUT):   end;
.
.
.
MLOGIC(PUT_FIELDS_OUT):  %DO loop index variable X is now 4; loop will not iterate again.
MPRINT(PUT_FIELDS_OUT):   put @1                 --Trying to get the following
&putdetail1 "$25." tab $1.                       --FULL_NAME $25 tab $1.
&putdetail2 "mmddyy10." tab $1.                  --DOB $25 tab $1.
&putdetail3 "10.2"                               --SALARY_YEARLY $25
;
MLOGIC(PUT_FIELDS_OUT):  Ending execution.
MPRINT(FILEOUT):  ;
MPRINT(FILEOUT):   run;

ERROR 22-322: Expecting a name.  

When I hard coded the macro names in the macro %put_fields_out, it seems to work fine:

%macro put_fields_out;
tab=('09'x);
if _n_ = 1 then do;
put @1
%do x = 1 %to &COL_COUNT;
 "&putheader&x" 
  %if &x < &COL_COUNT %then %do; tab $1. %end;
%end;
;
end;
put @1 
 &putdetail1 &putformat1 tab $1. /*hard coding instead */
 &putdetail2 &putformat2 tab $1. /*of using do-loop */
 &putdetail3 &putformat3 
;
%mend put_fields_out;

Obviously the plan is to use do-loop instead of hard coding the macro variables.

Has anybody come across with this challenge before?  Or does anybody have any suggestions how to solve this issue?

Thank you very much in advance.  I really appreciate you spending time to read this long post.

Have a great day.

Regards,

Yeti

[rogerjdeangelis · Accepted solution]

Have not spent much time on the post but
try changing the two occurances of

&putdetail&x "&putformat&x" 
to
&&putdetail&x "&putformat&x" 

%macro put_fields_out;
tab=('09'x);
if _n_ = 1 then do;
put @1
%do x = 1 %to &COL_COUNT;
      "&&putheader&x"          %if &x < &COL_COUNT %then %do; tab $1. %end;
%end;
;
end;
put @1
%do x = 1 %to &COL_COUNT;
      &&putdetail&x "&putformat&x"         %if &x < &COL_COUNT %then %do; tab $1. %end;
%end;
;
%mend put_fields_out;

%macro fileout;
data _null_;
      SET DETAIL;
      file 'd:/txt/fileout.txt';
      %put_fields_out;
run;
%mend fileout;

%CreateArrayFields;
%fileout;

[Yeti]

It is that simple. Ha!

Thank you so much for your quick response.

I tried it and it works.  I also had to edit the following:

                &&putdetail&x &&putformat&x tab $1.

Sweet.

Thank you again.  I love this community.

Have a great day.

[Tom]

I would avoid using macro variables at all since you already have the data in a dataset.

If I understand the question you have a metadata table with the headers, variables and formats and want to generate a data step to write a CSV file with a header row.

So why not just write the code to a program file. 

filename code temp ;
data _null_;
  set layout end=eof;
  file code ;
  if _n_=1 then put 'if _n_=1 then do;' ;
  put 'put ' header :$quote. '@;' ;
  if eof then put 'put ;' / 'end;' ;
run;
data _null_;
  set layout end=eof;
  file code mod;
  if _n_=1 then put 'put ' @ ;
  put column_names col_format  @ ;
  if eof then put ';' ;
run;

Then the step that actually writes the CSV file can be as simple as

data _null_;
  set have ;
  file "target.csv" dsd ;
  %include code / source2 ;
run;

So for you example LAYOUT file here is what the log might look like.

782  filename csv temp;
783  data _null_;
784    set have ;
785    file csv dsd ;
786    %include code / source2 ;
NOTE: %INCLUDE (level 1) file CODE is file ....
787 +if _n_=1 then do;
788 +put "Full Name" @;
789 +put "Date of Birth" @;
790 +put "Yearly Salary" @;
791 +put ;
792 +end;
793 +put FULL_NAME $25. DOB mmddyy10. SALARY_YEARLY 10.2 ;
NOTE: %INCLUDE (level 1) ending.
794  run;

So the only macro variables you need are the name of the input dataset with the metadata, the name of the dataset to be output and the name of the file to be written.

[Yeti]

Interesting, I will definitely have to try this out. I will report back with my results tomorrow. Thank you Tom.

[Yeti]

This makes sense Tom. I was not aware you could include a separate file within a file. This is very smart. I have to try this out as soon as I get a chance. Thank you again.

[Yeti]

I learnt two things today. && and include a separate file within a file. 😄

[Yeti]

I was able to implement this design successfully.  This much simpler, cleaner and easier to understand for the next guy.

Thank you very much Tom for the tip.  I really appreciate your help.

Regards,

Yeti