Reading csv from share drive into sas

Q1983 — Mon, 29 Jan 2018 19:46:20 GMT

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 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.

Re: Reading csv from share drive into sas

Reeza — Mon, 29 Jan 2018 19:51:54 GMT

You need a modified version of this.

http://documentation.sas.com/?docsetId=mcrolref&docsetTarget=n0ctmldxf23ixtn1kqsoh5bsgmg8.htm&docsetVersion=9.4&locale=en

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

Re: Reading csv from share drive into sas

ballardw — Mon, 29 Jan 2018 20:32:08 GMT

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;

topic Re: Reading csv from share drive into sas in SAS Programming

Reading csv from share drive into sas

Re: Reading csv from share drive into sas

Re: Reading csv from share drive into sas