topic Re: Problem importing my CSV into SAS University Edition in SAS Procedures

Problem importing my CSV into SAS University Edition

Quixotequest — Tue, 27 Jan 2015 04:17:44 GMT

Hello,

I am a new user and have been through tutorials practicing doing the statistical processes on my school project. I have prepared my data now and exported into CSV from my XLSX from SurveyMonkey.

I have tested that the CSV imports into other programs and data table looks good. I have now put the CSV into my shared folder. If I look into the My Folders showing in the SAS Studio interface I can see the file there.

Unfortunately, this program yields an error when running:

PROC IMPORT OUT = my_csv

FILE = "/myfolders/sasuser.v94/GFASurv_working_Lg_copy.csv"

DBMS = CSV

REPLACE;

RUN;

Gives this single error:

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

42 ;

43 PROC IMPORT OUT = my_csv

44 FILE = "/myfolders/sasuser.v94/GFASurv_working_Lg_copy.csv"

45 DBMS = CSV

46 REPLACE;

47 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: The path /myfolders/sasuser.v94/GFASurv_working_Lg_copy.csv is invalid because it is not in the list of accessible paths

when SAS is in the lockdown state.

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

cpu time 0.07 seconds

48 ;

49 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

59 ;

***

Can someone help me troubleshoot what I am doing wrong?

Re: Problem importing my CSV into SAS University Edition

Cynthia_sas — Tue, 27 Jan 2015 04:30:12 GMT

Hi:

Assuming you put your CSV file into your shared folders location, you need to use the /folders as the high level directory.

So this is WRONG:

FILE = "/myfolders/sasuser.v94/GFASurv_working_Lg_copy.csv"

and something like this would be right:

FILE = "/folders/myfolders/sasuser.v94/GFASurv_working_Lg_copy.csv"

The high level unix directory must be part of the path name.

cynthia

Re: Problem importing my CSV into SAS University Edition

Quixotequest — Tue, 27 Jan 2015 04:35:31 GMT

Thanks for the clarification. Making that change yields this error:

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

42 ;

43 PROC IMPORT OUT = my_csv

44 FILE = "/folders/myfolders/sasuser.v94/GFASurv_working_Lg_copy.csv"

45 DBMS = CSV

46 REPLACE;

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

Unable to sample external file, no data in first 5 records.

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

cpu time 0.11 seconds

48 ;

49 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

59 ;

Re: Problem importing my CSV into SAS University Edition

Quixotequest — Tue, 27 Jan 2015 16:38:20 GMT

, I was thinking I was getting this error perhaps because my survey data are ordinal data, so I deleted all records that were filtered out by the qualifying question, and filled all the blank cels of remaining responses with "null". I am still getting this report from the import. Can you help direct me what I should do?

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

42 ;

43 PROC IMPORT OUT = my_csv

44 FILE = "/folders/myfolders/sasuser.v94/GFASurvey_1s_Only_Parents.csv"

45 DBMS = CSV

46 REPLACE;

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

Unable to sample external file, no data in first 5 records.

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

cpu time 0.14 seconds

48 ;

49 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

59 ;

Re: Problem importing my CSV into SAS University Edition

Kurt_Bremser — Tue, 27 Jan 2015 16:44:38 GMT

Do this for testing:

data _null_;

infile "/folders/myfolders/sasuser.v94/GFASurv_working_Lg_copy.csv" obs=100;

input;

put _infile_;

run;

This should put the first 100 lines of the file, as SAS sees them, into the log.

Re: Problem importing my CSV into SAS University Edition

Quixotequest — Tue, 27 Jan 2015 16:53:54 GMT

Thank you. This code does yield an input from the first CSV I tried, as well as my latest, when I filled in the blank cels with "null":

(All record data were truncated from the copy/paste, but all 100 records appear to have listed from the report the code you gave me generated.)

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

42 ;

43 data _null_;

44 infile "/folders/myfolders/sasuser.v94/GFASurvey_1s_Only_Parents.csv" obs=100;

45 input;

46 put _infile_;

47 run;

NOTE: The infile "/folders/myfolders/sasuser.v94/GFASurvey_1s_Only_Parents.csv" is:

Filename=/folders/myfolders/sasuser.v94/GFASurvey_1s_Only_Parents.csv,

Owner Name=root,Group Name=vboxsf,

Access Permission=-rwxrwx---,

Last Modified=27Jan2015:11:32:07,

File Size (bytes)=229558

Q1-F1_Yes,Q1-F1_No,Q2-F2_-1,Q2-F2_1-2,Q2-F2_3+,Q3-F3_K-2,Q3-F3_3-4,Q3-F3_5-6,Q4-WT_1,Q4-WT_2,Q4-WT_3,Q4-WT_4,Q4-WT_5,Q4-WT_6,Q4-WT_7

,Q4-INN_1,Q4-INN_2,Q4-INN_3,Q4-INN_4,Q4-INN_5,Q4-INN_6,Q4-INN_7,Q4-ATT_1,Q4-ATT_2,Q4-ATT_3,Q4-ATT_4,Q4-ATT_5,Q4-ATT_6,Q4-ATT_7,Q4-Ab

leHmwk_1,Q4-AbleHmwk_2,Q4-AbleHmwk_3,Q4-AbleHmwk_4,Q4-AbleHmwk_5,Q4-AbleHmwk_6,Q4-AbleHmwk_7,Q4-USEFL_1,Q4-USEFL_2,Q4-USEFL_3,Q4-USE

FL_4,Q4-USEFL_5,Q4-USEFL_6,Q4-USEFL_7,Q4-MeetExp_1,Q4-MeetExp_2,Q4-MeetExp_3,Q4-MeetExp_4,Q4-MeetExp_5,Q4-MeetExp_6,Q4-MeetExp_7,Q5-

Math_1,Q5-Math_2,Q5-Math_3,Q5-Math_4,Q5-Math_5,Q5-Math_6,Q5-Math_7,Q5-Tests_1,Q5-Tests_2,Q5-Tests_3,Q5-Tests_4,Q5-Tests_5,Q5-Tests_6

,Q5-Tests_7,Q5-Methds_1,Q5-Methds_2,Q5-Methds_3,Q5-Methds_4,Q5-Methds_5,Q5-Methds_6,Q5-Methds_7,Q5-CoreK_1,Q5-CoreK_2,Q5-CoreK_3,Q5-

CoreK_4,Q5-CoreK_5,Q5-CoreK_6,Q5-CoreK_7,Q5-Acad_1,Q5-Acad_2,Q5-Acad_3,Q5-Acad_4,Q5-Acad_5,Q5-Acad_6,Q5-Acad_7,Q5-ExCur_1,Q5-ExCur_2

,Q5-ExCur_3,Q5-ExCur_4,Q5-ExCur_5,Q5-ExCur_6,Q5-ExCur_7,Q6-Grd-NI,Q6-Grd-SI,Q6-Grd-CI,Q6-Com-NI,Q6-Com-SI,Q6-Com-CI,Q6-PTM-NI,Q6-PTM

-SI,Q6-PTM-CI,Q7-AcaPerf-NI,Q7-AcaPerf-SI,Q7-AcaPerf-CI,Q7-GoalPrep-NI,Q7-GoalPrep-SI,Q7-GoalPrep-CI,Q8-Kind_1,Q8-Kind_2,Q8-Kind_3,Q

8-Kind_4,Q8-Kind_5,Q8-Kind_6,Q8-Kind_7,Q8-WrkTog_1,Q8-WrkTog_2,Q8-WrkTog_3,Q8-WrkTog_4,Q8-WrkTog_5,Q8-WrkTog_6,Q8-WrkTog_7,Q8-Persv_

1,Q8-Persv_2,Q8-Persv_3,Q8-Persv_4,Q8-Persv_5,Q8-Persv_6,Q8-Persv_7,Q8-Achv_1,Q8-Achv_2,Q8-Achv_3,Q8-Achv_4,Q8-Achv_5,Q8-Achv_6,Q8-A

chv_7,Q8-WelOrg_1,Q8-WelOrg_2,Q8-WelOrg_3,Q8-WelOrg_4,Q8-WelOrg_5,Q8-WelOrg_6,Q8-WelOrg_7,Q8-HQAcad_1,Q8-HQAcad_2,Q8-HQAcad_3,Q8-HQA

cad_4,Q8-HQAcad_5,Q8-HQAcad_6,Q8-HQAcad_7,Q8-RegCom_1,Q8-RegCom_2,Q8-RegCom_3,Q8-RegCom_4,Q8-RegCom_5,Q8-RegCom_6,Q8-RegCom_7,Q8-Hap

py_1,Q8-Happy_2,Q8-Happy_3,Q8-Happy_4,Q8-Happy_5,Q8-Happy_6,Q8-Happy_7,Q8-After_1,Q8-After_2,Q8-After_3,Q8-After_4,Q8-After_5,Q8-Aft

er_6,Q8-After_7,Q9-RecLookng_1,Q9-RecLookng_2,Q9-RecLookng_3,Q9-RecLookng_4,Q9-RecLookng_5,Q9-RecLookng_6,Q9-RecLookng_7,Q9-SMTalk_1

,Q9-SMTalk_2,Q9-SMTalk_3,Q9-SMTalk_4,Q9-SMTalk_5,Q9-SMTalk_6,Q9-SMTalk_7,Q9-SMRecm_1,Q9-SMRecm_2,Q9-SMRecm_3,Q9-SMRecm_4,Q9-SMRecm_5

,Q9-SMRecm_6,Q9-SMRecm_7,Q9-Altern_1,Q9-Altern_2,Q9-Altern_3,Q9-Altern_4,Q9-Altern_5,Q9-Altern_6,Q9-Altern_7,Q9-PEvent_1,Q9-PEvent_2

,Q9-PEvent_3,Q9-PEvent_4,Q9-PEvent_5,Q9-PEvent_6,Q9-PEvent_7,Q10-No,Q10-Y/N,Q10-Y_1-2,Q10-Y_3+,Q11-Cultr_1,Q11-Cultr_2,Q11-Cultr_3,Q

11-Cultr_4,Q11-Cultr_5,Q11-Cultr_6,Q11-Cultr_7,Q11-Atitud_1,Q11-Atitud_2,Q11-Atitud_3,Q11-Atitud_4,Q11-Atitud_5,Q11-Atitud_6,Q11-Ati

tud_7,Q11-Staff_1,Q11-Staff_2,Q11-Staff_3,Q11-Staff_4,Q11-Staff_5,Q11-Staff_6,Q11-Staff_7,Q11-StudBehv_1,Q11-StudBehv_2,Q11-StudBehv

_3,Q11-StudBehv_4,Q11-StudBehv_5,Q11-StudBehv_6,Q11-StudBehv_7,Q11-Curic_1,Q11-Curic_2,Q11-Curic_3,Q11-Curic_4,Q11-Curic_5,Q11-Curic

_6,Q11-Curic_7,Q11-Spirit_1,Q11-Spirit_2,Q11-Spirit_3,Q11-Spirit_4,Q11-Spirit_5,Q11-Spirit_6,Q11-Spirit_7,Q11-Prspct_1,Q11-Prspct_2,

Q11-Prspct_3,Q11-Prspct_4,Q11-Prspct_5,Q11-Prspct_6,Q11-Prspct_7,Q12_Carac_NI,Q12_Carac_SI,Q12_Carac_CI,Q13-SpkKids_1,Q13-SpkKids_2,

Q13-SpkKids_3,Q13-SpkKids_4,Q13-SpkKids_5,Q13-SpkKids_6,Q13-SpkKids_7,Q13-TalkOthrs_1,Q13-TalkOthrs_2,Q13-TalkOthrs_3,Q13-TalkOthrs_

4,Q13-TalkOthrs_5,Q13-TalkOthrs_6,Q13-TalkOthrs_7,Q13-HearOthrs_1,Q13-HearOthrs_2,Q13-HearOthrs_3,Q13-HearOthrs_4,Q13-HearOthrs_5,Q1

3-HearOthrs_6,Q13-HearOthrs_7,Q13-Influ_1,Q13-Influ_2,Q13-Influ_3,Q13-Influ_4,Q13-Influ_5,Q13-Influ_6,Q13-Influ_7,Q14_Yes,Q14_No,Q15

_Open,Q16_Wk,Q16_Mos,Q16_Yr,Q17_1-2,Q17_3-5,Q17_6-9,Q17_10+,Q18_Time,Q18_Budget,Q18_Needs,Q18_NoContact,Q18_MoSocial,Q18_Before,Q18_

NoAppr,Q18_Other,Q19_CRoom_Hv,Q19_CRoom_Wdo,Q19_Facil_Hv,Q19_Facil_Wdo,Q19_StdtSupp_Hv,Q19_StdtSupp_Wdo,Q19_Events_Hv,Q19_Events_Wdo

,Q19_Lead_Hv,Q19_Lead_Wdo,Q19_Publ_Hv,Q19_Publ_Wdo,Q19_Financ_Hv,Q19_Financ_Wdo,Q19_Other,Q20_DontKnow,Q20_Motiv,Q20_OthrReas,Q20_Fo

rget,Q20_OKSugg,Q20_Change,Q20_OthrPPL,Q20_Other,Q21-WoM_Hr,Q21-WoM_Pref,Q21-Flyr_Hr,Q21-Flyr_Pref,Q21-Newslt_Hr,Q21-Newslt_Pref,Q21

-BB_Hr,Q21-BB_Pref,Q21-FB_Hr,Q21-FB_Pref,Q21-Email_Hr,Q21-Email_Pref,Q21-Phone_Hr,Q21-Phone_Pref,Q21-PTO_Hr,Q21-PTO_Pref,Q21-Board_H

r,Q21-Board_Pref
1,null,null,null,1,null,1,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null

,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,n

ull,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,nul

l,nu

Re: Problem importing my CSV into SAS University Edition

Kurt_Bremser — Tue, 27 Jan 2015 17:10:19 GMT

Maybe SAS has a problem generating variable names from your first line?

Try reading the file "manually" in a data step with correct DLM, TERMSTR and FIRSTOBS options.

Re: Problem importing my CSV into SAS University Edition

Quixotequest — Thu, 05 Feb 2015 18:58:02 GMT

I turns out the problem was with dashes and numbers appearing in my column heading names. Once they were replaced in my XLS file with underscores it imported just fine with code:

PROC IMPORT DATAFILE = "/folders/myfolders/sasuser.v94/<my file name>.xls"

OUT = <file name created for the SAS library>

DBMS = XLS

REPLACE;

GETNAMES=YES;

RUN;

Thanks for the help!

Re: Problem importing my CSV into SAS University Edition

Kurt_Bremser — Fri, 06 Feb 2015 07:36:38 GMT

This suggests that you would need to set the option validvarname=any, which I personally would not recommend. It is better to use basic valid SAS names throughout, especially when you are writing code, you can avoid the 'xxxx'n constructs.

Re: Problem importing my CSV into SAS University Edition

ballardw — Fri, 06 Feb 2015 16:02:01 GMT

Also notice that you changed from reading CSV to XLS. So was the original file actually CSV or not? Generally SAS replaces any non-letter, number or _ character into an underscore when reading CSV but it would fail if the actual file was XLS but with a CSV extension, not an actual CSV file.

Re: Problem importing my CSV into SAS University Edition

Quixotequest — Fri, 06 Feb 2015 18:06:54 GMT

Good point. Yes it was originally an XLS. When it didn't import I read that a CSV would be more reliable and so I exported to CSV. Once I figured out the column naming issue then I just fixed within XLS and voila! It imported.

Re: Problem importing my CSV into SAS University Edition

xoc3 — Wed, 11 Feb 2015 16:43:00 GMT

Thanks Cynthia, I had similar problem and adding the "folders" worked like a charm!