BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Yeti
Calcite | Level 5

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;

JOHNDOE.png

 

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
rogerjdeangelis
Barite | Level 11
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;

View solution in original post

7 REPLIES 7
rogerjdeangelis
Barite | Level 11
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
Calcite | Level 5

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
Super User Tom
Super User

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
Calcite | Level 5
Interesting, I will definitely have to try this out. I will report back with my results tomorrow. Thank you Tom.
Yeti
Calcite | Level 5
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
Calcite | Level 5
I learnt two things today. && and include a separate file within a file. 😄
Yeti
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 3380 views
  • 0 likes
  • 3 in conversation