topic Re: DO LOOP Macro in New SAS User

DO LOOP Macro

craigwe85 — Tue, 30 Oct 2018 17:35:39 GMT

Hello,

I have the current code that goes to a weather website a downloads the data into SAS. The station macro, inputs the corresponding station id into the URL and then pulls the weather for that city. I will be inputting about 100+ cities. Instead of adding each individual station id to the macro below, is there a way to utilize a do loop macro that would go through a list of station id's that i will save as a sas dataset? i will be saving the new data set which will contain Station ID and City and calling it Weather_Stations.

%macro station (stat=);

filename in url "http://climate.weather.gc.ca/climate_data/bulk_data_e.html?format=csv&stationID=&stat&Year=2018&Month=10&Day=1&timeframe=2&submit=Download+Data";
filename out "%sysfunc(pathname(work))\data.csv";                                                                                                                 
                                                                                                                                                                  
data _null_;                                                                                                                                                      
  infile in;                                                                                                                                                      
  input;                                                                                                                                                          
  if _n_> 25;                                                                                                                                                     
  file out;                                                                                                                                                       
  put _infile_;                                                                                                                                                   
run;                                                                                                                                                              
                                                                                                                                                                  
PROC IMPORT OUT= WORK.data_&stat                                                                                                                                        
     DATAFILE= "%sysfunc(pathname(work))\data.csv"                                                                                                                
     DBMS=CSV REPLACE;                                                                                                                                            
RUN;
%mend;

%station (stat=52518);/*Sydgary*/
%station (stat=50149);/*Edmonton */
%station (stat=50837);/*Fort St.John*/
%station (stat=48369);/*Kelowananey*/
%station (stat=50620);/*Halifax*/
%station (stat=50309);/*Moncton*/
%station (stat=50068);/*Deer Lake*/
%station (stat=50089);/*St.Johns*/
%station (stat=50621);/*Chartown*/
%station (stat=51157);/*Montreal*/
%station (stat=26892);/*Quebec*/
%station (stat=51459);/*Toronto*/
%station (stat=49568);/*Ottawa */
%station (stat=54604);/*North Bay*/
%station (stat=27174);/*Winnipeg*/
%station (stat=28011);/*Regina*/
%station (stat=47707);/*Saskatoon*/
%station (stat=50430);/*Cal*/
%station (stat=51442);/*Vancouver*/
%station (stat=51337);/*Victoria*/

Re: DO LOOP Macro

novinosrin — Tue, 30 Oct 2018 17:42:22 GMT

You could store you station in a dataset and Call execute

Re: DO LOOP Macro

PaigeMiller — Tue, 30 Oct 2018 17:43:55 GMT

Yes, if your city codes are in a SAS data set (and are stored as a character string named CITY_CODE), you can use CALL EXECUTE to call the macro

Something like this

data _null_;
    set have;
    call execute('%station(stat='||city_code||')');
run;

Re: DO LOOP Macro

novinosrin — Tue, 30 Oct 2018 17:44:35 GMT

@PaigeMiller I am glad my brain is functioning alike you. Hopefully you bless me with your statistical skills and soon I can get good at those well. 🙂

Re: DO LOOP Macro

PaigeMiller — Tue, 30 Oct 2018 17:49:17 GMT

@novinosrin wrote:

@PaigeMiller I am glad my brain is functioning alike you. Hopefully you bless me with your statistical skills and soon I can get good at those well. 🙂

I'm sure you will. It just takes time.

Re: DO LOOP Macro

AMSAS — Tue, 30 Oct 2018 17:54:14 GMT

Try this example:


/* Set up test data */
/* this would be your dataset containing your station Ids */
data test ;
	do station_id=1000 to 1020 by 3 ;
		output ;
	end ;
run ;


/* Create macro to loop through the station Ids */
/* Pass in the station ID dataset and the variable name containing the station Ids */
%macro loop(inputDataset,stationVar) ;
	%put &inputDataset= &stationVar= ;
	/* 
	Datastep will create multiple macro variables named station_id<n> 
	where <n> is a number 1+, and containing the station Ids
	Create a count of all station Ids
	*/
	data _null_ ;
		set &inputDataset ;
		put _all_ ;
		
		call symput("station_id"!!left(putn(_n_,"10.")),putn(&stationVar,"10.")) ;
		call symput("count",putn(_n_,"10.")) ;
	run ;
	
	/* 
	Loop through all the macro variables printinng their values
	Note you would call your station macro inside this loop 
	*/
	%do i=1 %to &count ;
		%put &count &i &&station_id&i ;
		
	%end ;

%mend ;

%loop(test,station_id) ;

Re: DO LOOP Macro

craigwe85 — Tue, 30 Oct 2018 19:22:38 GMT

@PaigeMiller Thanks so much. Saved so much time. Now I can leave work early 🙂

Re: DO LOOP Macro

craigwe85 — Tue, 16 Jul 2019 18:49:58 GMT

Sorry I realize this post is old,

however I am still using this technique and was wondering if it were possible to add more variables to the call execute function?

so I can create unique tables names and add an additional where statement if needed?

Re: DO LOOP Macro

Tom — Tue, 16 Jul 2019 19:18:51 GMT

Yes.

Just make sure to generate valid SAS code.

Using the CAT... series of functions can help.

%macro mymacro(var1=,var2=,var3=);
 .... 
%mend;

data _null_;
  set mydata ;
  call execute(cats('%nrstr(%mymacro)','(var1=',var1,',var2=',var2,',var3=',var3,')'));
run;