output data from macro to text file

[LB]

So I am a little rusty on macros -- Below two things happen -first I output a dataset with dates (Thanks @Reeza  - I took this from you) . The next part I am using the dataset to attempt to output to a text file-

Once I figure out how to do it, I can expand on what I need to do-for now  I just can't figure how to successfully capture the observations - TIA. 

data arghh;
Do year=2013 to 2016;
Do month=1 to 12;
n='N'||COMPRESS(PUT(MONTH,8.)||PUT(YEAR,8.));
Date1=mdy(month, 1, year);
date2=intnx('month',date1,0,'end');
date1T=put(date1,ddmmyy.);
date2T=put(date2,ddmmyy.);
Output;
End;
End;
format date1 date9. date2 date9.;
run;

%macro roller(data=, var=,var1=,var2=);
   proc sort data=&data(keep=&var &var1 &var2) out=values nodupkey;
    by &var  ;
   run;
   data _null_;
   file 'test2.txt';
      set values end=last;
      call symputx('siteA'||left(_n_),&var);
	  call symputx('DateA'||left(_n_),&var1);
	  call symputx('DateB'||left(_n_),&var2);
      if last then call symputx('countx',_n_,'g');
   run;

select(&var);


%do i=1 %to &countx; 
%PUT "A &siteA&i";
%PUT "A &&DateA&i";
%PUT "B &&DateB&i";

run;
 file print; /*doesn't seem to work here*/ 
 %END;

 %MEND roller;

%roller(data=arghh, var=N,var1=date1T,var2=date2T);

[ballardw]

If you mean the values of macro variables you have two basic choices.

Best is likely to place the values into a data set then all of the Proc Print/Tabulate/Report or Put options in a data step are available.

Or us %put to write the values to the Log while using Proc Printto to send Log output to file.

Run; ends a data step. So code after that is outside of a valid data step and throws errors. Your Select, when needs and End statement, would have to be before the first run, as would the File Print; Plus it is not clear what the select is supposed to do.

Due to timing of code execution vs compilation then use of macro statements such as %put inside a data step seldom does what is desired.

Dummy code of what I think you may have intended:

Data _null_

<create macro vars>

run;

Proc printto  log='path\outputfile.txt';

run;

%do i=1 %to &countx;
%PUT "A &siteA&i";
%PUT "A &&DateA&i";
%PUT "B &&DateB&i";
%end;

/* stop sendit %put to file*/

proc printto;

run;

<end the macro>

And generally any code that places lots of data step values into macro variables needs to be reconsidered. Just how are you going to use all of those site and date macro variables?

[LB]

I had thought about this but reading back in file and data clean would be a pain- In short what I will be doing and what I have done in the past (if i could just remember what I did) was to essentially export a text file as a SAS program for the purposes of executing it on the SAS Grid at the same time. I know there is a way to do this as I have done in the past-just don't have access to the code...

[JerryV]

It's not quite clear to me what you want your text file to look like.  Can you provide a sample of what you expect your text file to contain?  

Your %put statements write out something almost like this.  Is this what should be in your text file?  

"A N102013"
"A 01/10/13"
"B 31/10/13"
"A N102014"
"A 01/10/14"
"B 31/10/14"
...

[LB]

@ballardw  and @JerryV  - This is what the text will look like generally and roughly when outputted but you get the idea- I need to fix it so that it will export out the ; at the end.  Using a file statement  within the macro and put statements without the % results in the macros not being invoked. I changed the code so it is mostly there- I will prob go with the proc printto concept and then can clean up the file and re-export to a SAS file and execute. 

%macro roller(data=, var=,var1=,var2=);
   proc sort data=&data(keep=&var &var1 &var2) out=values nodupkey;
    by &var  ;
   run;
   data _null_;
  
      set values end=last;
      call symputx('siteA'||left(_n_),&var);
	  call symputx('DateA'||left(_n_),&var1);
	  call symputx('DateB'||left(_n_),&var2);
      if last then call symputx('countx',_n_,'g');
   run;

data _null_;
 Proc printto  log='outputfile.txt';

run;

%do i=1 %to &countx; 
%PUT signon  &&siteA&i;
%PUT rsubmit  &&siteA&i;

%PUT  proc sql (sysfunc here for ';'); 
%PUT SELECT * FROM BLAH WHERE XYZ between "&&DateA&i" and "&&DateB&i"; 
%put Quit';';
%put endrsubmit;

run;


 %END;
proc printto;
 %MEND roller;

%roller(data=arghh, var=N,var1=date1T,var2=date2T);

[Tom · Accepted solution]

There is no need to move the data into macro variables. Just use a data step to write the file. Use the PUT statement.

If you want the data step to write multiple files use the FILENAME= option on the FILE statement.

filename code temp;
data _null_;
   file code;
   set mydata ;
   put 'signon ' siteA ';'
     / 'rsubmit ' siteA ';'
     / 'proc sql;'
     / 'select * from BLAH where XYZ between ''' dataA ''' and ''' dateb ''';'
     / 'quit;'
     / 'endrsubmit;'
  ;
run;

If you have a bunch of commands for the same site you might want to sort the dataset by SITEA and use FIRST. and LAST. processing to generate the code all together.

[ballardw]

I will suggest again:

Show the values in the data set and the exact lines of text that are supposed to go into the text file.

Having stuff like " (sysfunc here for ';') " does not show what text you need to generate.

I suspect that picture is incomplete because RSUBMIT and ENDRSUBMIT are likley missing semicolons as well.

You may also not be familiar with the data step CALL EXECUTE statement. You can use a data step with an input data set to contain changing values and create lines of code to execute. The statements get put into the execution stack and start after the data step completes.

A very simple example with one record in the a data set:

data example;
   dsname ='sashelp.class';
run;

data _null_;
   set example;
   Call execute ("proc print data=");
   call execute (dsname);
   call execute ("; run;");
run;

You can see that the first and third call execute have basic "boiler plate" or static instructions. The variable value gets stuck in after the data=. This is what the log would look like:

1   + proc print data=
2   + sashelp.class
3   + ; run;

so you can see the code generated and the results.

But the idea is the same. We have to know the exact text that would be generated.

[LB]

Thank you -also very useful to know for future reference.

[LB]

Thanks.   This bypasses the macro necessity and make things a lot simpler. 

[ballardw]

Create a string value in a data step. Put the value.

data _null_;
   file "<path>\output.txt";
   Put "some literal text to write to the file";
   Put "some other text";
   /* if the value is in data step variables*/
   put "the value for variable x is " x; /*<= may need to specify a format for x*/
run;

I didn't see anything that looked like an attempt to write instructions, just placing a bunch of data step value into macro variables.

If you need multiple variables on the same line in the text they go in the same PUT statement. If you need to control columns that things output to then the pointer controls like +1 (to move the pointer to the right 1 space) or @25 to print a value at column 25 in the text file.

Hint:

Show a short example of the data you have and then an example of what the text file should look like.

Paste the example text file into a code box opened on the forum with the </> or the results are likely to get reformatted and not actually be what was intended.