DATA Step, Macro, Functions and more

Reading csv from share drive into sas

Accepted Solution Solved
Reply
Regular Contributor
Posts: 178
Accepted Solution

Reading csv from share drive into sas

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.


Accepted Solutions
Solution
‎01-29-2018 04:44 PM
Super User
Posts: 23,237

Re: Reading csv from share drive into sas

You need a modified version of this.

http://documentation.sas.com/?docsetId=mcrolref&docsetTarget=n0ctmldxf23ixtn1kqsoh5bsgmg8.htm&docset...

 

The appendix also has examples of looping through lists, though I'm partial to CALL EXECUTE myself.

 

 

View solution in original post


All Replies
Solution
‎01-29-2018 04:44 PM
Super User
Posts: 23,237

Re: Reading csv from share drive into sas

You need a modified version of this.

http://documentation.sas.com/?docsetId=mcrolref&docsetTarget=n0ctmldxf23ixtn1kqsoh5bsgmg8.htm&docset...

 

The appendix also has examples of looping through lists, though I'm partial to CALL EXECUTE myself.

 

 

Super User
Posts: 13,293

Re: Reading csv from share drive into sas

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;

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 96 views
  • 2 likes
  • 3 in conversation