Problem reading ZIP File With SAS

[telligent]

Any clue as to what I am doing wrong here?

The error: 

ERROR: Open failure for /Users/xxxxxxx/My Folder/FiveYearNational_Awardees.zip during attempt to create a local file handle.

The code

Filename treat zip "/Users/xxxxxxx/My Folder/FiveYearNational_Awardees.zip";
filename target "%sysfunc(getoption(work))/2022_National_Awardees.xlsx" ;

/* Read ZIP like a directory with DOPEN */
data _contents(keep=memname);
length memname $200;
fid=dopen("treat");
if fid=0 then stop;
memcount=dnum(fid);
do i=1 to memcount;
memname=dread(fid,i);
output;
end;
rc=dclose(fid);
run;

data _null_;
/* using member syntax here */
infile treat (2022_National_Awardees.xlsx)
lrecl=256 recfm=F length=length eof=eof unbuf;
file target lrecl=256 recfm=N;
input;
put _infile_ $varying256. length;
return;
eof:
stop;
run;

[ChrisHemedinger]

@telligent Thanks for commenting on my SAS Explore presentation ( Reading and Writing ZIP Files With SAS )

The error you shared isn't really enough to see what's going on. It could be the filename is incorrect (remember that case matters in a UNIX/Linux environment). But perhaps if you can share more of the log we can help.

[telligent]

Since it is a publicly available file, I just attached it. I am trying to read it in SAS Studio in Viya where I uploaded the zip file. I am also having trouble reading the multiple excel tabs that are in the excel files that are in the zip and I found the pdf of your presentation on that as well. So, if you could help with both reading the zip file and reading the tabs, that would be great!

[yabwon]

Does "xxxxxxx" contain any non ascii (e.g. some UTF-8) characters? Like local language letters?

Is it sas 9.4M8 or lower?

Bart

[telligent]

No that is where my name would be.  I just deidentified it, but it is all letters.  

[Tom]

So where are you getting stuck?  Can you read the members in the ZIP file?

%let fname=FiveYearNational_Awardees.zip;

filename xx zip "c:\downloads\&fname";

data names ;
  did=dopen('xx');
  do i=1 to dnum(did);
    length name $200;
    name=dread(did,i);
    output;
  end;
  did=dclose(did);
run;

proc print;
run;

Result

Obs    did    i               name

 1      1     1    2018_National_Awardees.xlsx
 2      1     2    2019_National_Awardees.xlsx
 3      1     3    2020_National_Awardees.xlsx
 4      1     4    2021_National_Awardees.xlsx
 5      1     5    2022_National_Awardees.xlsx

Can you copy one of the members to an actual file?

191  filename xx;
NOTE: Fileref XX has been deassigned.
192  filename in zip "c:\downloads\&fname" member="2018_National_Awardees.xlsx" recfm=n lrecl=512;
193  filename out "%sysfunc(pathname(work))\2018_National_Awardees.xlsx" recfm=n lrecl=512;
194  %put rc = %sysfunc(fcopy(in,out));
rc = 0
195  filename in;
NOTE: Fileref IN has been deassigned.
196  filename out;
NOTE: Fileref OUT has been deassigned.

Can you copy the sheets in that XLSX file?

libname in xlsx "%sysfunc(pathname(work))\2018_National_Awardees.xlsx";
proc copy inlib=in outlib=work;
run;

This is where I get errors because the sheets in that file have names that are not valid SAS dataset names.

ERROR: The value 2018  AGE AND RACE-ETHNICITY is not a valid SAS name.

So you will need to set the option VALIDMEMNAME to EXTEND to be able to use those as the name of the dataset to read.  

But you will also need to do more to make valid names for the datasets you want to create.

You can use PROC CONTENTS (or dictionary views) to get the list of MEMNAME's in the XLSX file. 

Then use those to generate code to create SAS datasets with valid names.

875  data test;
876    set in.'2018  AGE AND RACE-ETHNICITY'n ;
NOTE:    Variable Name Change.  Health Center Name -> Health_Center_Name
NOTE:    Variable Name Change.  Total Patients -> Total_Patients
NOTE:    Variable Name Change.  Children (< 18 years old) -> VAR5
NOTE:    Variable Name Change.  Adult (18 - 64) -> VAR6
NOTE:    Variable Name Change.  Older Adults (age 65 and over) -> Older_Adults__age_65_and_over_
NOTE:    Variable Name Change.  Racial and/or Ethnic Minority -> Racial_and_or_Ethnic_Minority
NOTE:    Variable Name Change.  Hispanic/Latino Ethnicity -> Hispanic_Latino_Ethnicity
NOTE:    Variable Name Change.  Black/African American -> Black_African_American
NOTE:    Variable Name Change.  American Indian/Alaska Native -> American_Indian_Alaska_Native
NOTE:    Variable Name Change.  Native Hawaiian / Other Pacific -> Native_Hawaiian___Other_Pacific
NOTE:    Variable Name Change.  More than one race -> More_than_one_race
NOTE:    Variable Name Change.  Best Served in another language -> Best_Served_in_another_language
877    run;

NOTE: The import data set has 1362 observations and 15 variables.
NOTE: There were 1362 observations read from the data set IN.'2018  AGE AND RACE-ETHNICITY'n.
NOTE: The data set WORK.TEST has 1362 observations and 15 variables.
NOTE: DATA statement used (Total process time):
      real time           0.22 seconds
      cpu time            0.23 seconds

[telligent]

Thank you but would you mind doing it from SAS studio in Viya instead of off your c drive.  I think it may the whole cloud thing that has me confused.  I never have problems like this in 9.4.