I have a share drive with about 10 states named by folder (ie al az dc etc...) Within those folders there may be a .csv file or may not be one.
I am attempting to say if it exists import the contents of each folder into one dataset in sas. Here is what I have so far
%let myPath =/myshare/HHFit;
%put &myPath;
%Let StateList= AL az ca dc ehlp FL ga il in ky ;
%macro fetchstates(Statelist= , Currdate= );
%Let cnt=1;
%Let Thisstate=%scan(&StateList, &cnt, %Str( ) );
%Do %While (&Thisstate NE );
proc import out= &Thisstate
datafile="&myPath/&Thisstate./&Thisstate._HHF_STM_ToState_&Currdate_key._01.csv"
dbms=CSV replace ;
getnames=no;
datarow=3;
run;
/*** Advance counter and select next state ***/
%Let cnt=%eval(&cnt + 1);
%Let Thisstate=%scan(&StateList, &cnt, %Str( ) );
%End;
%Mend;
%fetchstates(StateList=&StateList,
Currdate=&Currdate_key);
I get the following error. It is unable to open the parameter and says file does not exists. In some cases theres no file however theres a file for AL (Alabama). The program is supposed to loop through all folders and grab the cvs if it exists.
SYMBOLGEN: Macro variable THISSTATE resolves to AL
SYMBOLGEN: Macro variable DORSHHFPATH resolves to /myshare/HHFit
SYMBOLGEN: Macro variable THISSTATE resolves to AL
SYMBOLGEN: Macro variable THISSTATE resolves to AL
SYMBOLGEN: Macro variable CURRDATE_KEY resolves to 20180129
MPRINT(FETCHSTATES): proc import out= AL datafile="/myshare/HHFit/AL/AL_HHF_STM_ToState_20180129_01.csv" dbms=CSV replace ;
MPRINT(FETCHSTATES): ADLM;
MPRINT(FETCHSTATES): getnames=no;
MPRINT(FETCHSTATES): datarow=3;
MPRINT(FETCHSTATES): run;
NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to WORK.PARMS.PARMS.SLIST.
ERROR: Physical file does not exist, /myshare/HHFit/AL/AL_HHF_STM_ToState_20180129_01.csv.
ERROR: Import unsuccessful. See SAS Log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.11 seconds
cpu time 0.01 seconds
RROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: &Thisstate NE
ERROR: The condition in the %DO %WHILE loop, , yielded an invalid or missing value, . The macro will stop executing.
ERROR: The macro FETCHSTATES will stop executing.
You need a modified version of this.
The appendix also has examples of looping through lists, though I'm partial to CALL EXECUTE myself.
You need a modified version of this.
The appendix also has examples of looping through lists, though I'm partial to CALL EXECUTE myself.
This error
ERROR: Physical file does not exist, /myshare/HHFit/AL/AL_HHF_STM_ToState_20180129_01.csv.
may be caused by running in a server environment as the path may need to be relative to the server.
The macro language really doesn't make it simple to check for "blank" values. but this
%Let cnt=1; %Let Thisstate=%scan(&StateList, &cnt, %Str( ) ); %Do %While (&Thisstate NE );
could be replaced with something like:
%Let Listcnt= %sysfunct(countw(&StateList)); %do i = 1 %to &Listcount; %Let Thisstate=%scan(&StateList,&i );
and instead of incrementing the cnt variable and extracting thisstate just use %end;
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.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.