topic Re: proc import csv file in sas studio healthcare data in SAS Procedures

proc import csv file in sas studio healthcare data

pandyat0 — Tue, 04 Oct 2016 02:42:18 GMT

Hello,

I am using proc import a file and I cleaned up titles (headers) with no space and headers no more than 13 letters. I also have code below. Just so you know, I am running SAS Studio on SAS Academy on Linux. Does it matter? Can you please tell me what is the problem and how do I resolve it? Thank you in advance. I also have head of data set for you to see below.

Libname OP2013"/saswork/SAS_work6A1C0000FE43_odaws04-prod-us/SAS_work0D5C0000FE43_odaws04-prod-us";
FILENAME op2013"/home/pandyat0/sasuser.v94/Outpatient_2013.txt" TERMSTR=CRLF;
/** Import the CSV file.  **/
PROC IMPORT DATAFILE=op2013
		    OUT=WORK.op2013
		    DBMS=CSV
		    REPLACE;
		    GETNAMES=Yes;
RUN;

ERROR SHOWN BELOW:

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.03 seconds
       user cpu time       0.01 seconds
       system cpu time     0.02 seconds
       memory              8451.00k
       OS Memory           36124.00k
       Timestamp           10/04/2016 02:27:57 AM
       Step Count                        37  Switch Count  50
       Page Faults                       0
       Page Reclaims                     925
       Page Swaps                        0
       Voluntary Context Switches        158
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           48
       
 62         
 63         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 75

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.

DATASET:

APCG,ProvId,ProvName,ProvStrAd,ProvCity,ProvSt,ProvZipC,ProvHRR,OPServs,AvgEstSubChrg,AvgTotPay
0013-LevelIIDebridement&Destruction,10001,SOUTHEASTALABAMAMEDICALCENTER,1108ROSSCLARKCIRCLE,DOTHAN,AL,36301,AL-Dothan,639,$391.64,$54.94
0015-LevelIIIDebridement&Destruction,10001,SOUTHEASTALABAMAMEDICALCENTER,1108ROSSCLARKCIRCLE,DOTHAN,AL,36301,AL-Dothan,503,$595.82,$83.51
0019-LevelIExcision/Biopsy,10001,SOUTHEASTALABAMAMEDICALCENTER,1108ROSSCLARKCIRCLE,DOTHAN,AL,36301,AL-Dothan,23,$3531.88,$254.15
0020-LevelIIExcision/Biopsy,10001,SOUTHEASTALABAMAMEDICALCENTER,1108ROSSCLARKCIRCLE,DOTHAN,AL,36301,AL-Dothan,32,$4596.94,$507.30
0078-LevelIIIPulmonaryTreatment,10001,SOUTHEASTALABAMAMEDICALCENTER,1108ROSSCLARKCIRCLE,DOTHAN,AL,36301,AL-Dothan,142,$218.82,$87.60
0096-LevelIINoninvasivePhysiologicStudies,10001,SOUTHEASTALABAMAMEDICALCENTER,1108ROSSCLARKCIRCLE,DOTHAN,AL,36301,AL-Dothan,363,$988.04,$93.59
0204-LevelINerveInjections,10001,SOUTHEASTALABAMAMEDICALCENTER,1108ROSSCLARKCIRCLE,DOTHAN,AL,36301,AL-Dothan,385,$2084.46,$147.57
0206-LevelIINerveInjections,10001,SOUTHEASTALABAMAMEDICALCENTER,1108ROSSCLARKCIRCLE,DOTHAN,AL,36301,AL-Dothan,67,$1438.85,$240.75
0207-LevelIIINerveInjections,10001,SOUTHEASTALABAMAMEDICALCENTER,1108ROSSCLARKCIRCLE,DOTHAN,AL,36301,AL-Dothan,3169,$2228.79,$480.49
"0209-LevelIIExtendedEEG,Sleep,andCardiovascularStudies",10001,SOUTHEASTALABAMAMEDICALCENTER,1108ROSSCLARKCIRCLE,DOTHAN,AL,36301,AL-Dothan,729,$4468.12,$697.15
0265-LevelIDiagnosticandScreeningUltrasound,10001,SOUTHEASTALABAMAMEDICALCENTER,1108ROSSCLARKCIRCLE,DOTHAN,AL,36301,AL-Dothan,279,$805.98,$56.25
0267-LevelIIIDiagnosticandScreeningUltrasound,10001,SOUTHEASTALABAMAMEDICALCENTER,1108ROSSCLARKCIRCLE,DOTHAN,AL,36301,AL-Dothan,1737,$858.17,$134.91
0269-LevelIIEchocardiogramWithoutContrast,10001,SOUTHEASTALABAMAMEDICALCENTER,1108ROSSCLARKCIRCLE,DOTHAN,AL,36301,AL-Dothan,844,$2024.28,$339.31
0270-LevelIIIEchocardiogramWithoutContrast,10001,SOUTHEASTALABAMAMEDICALCENTER,1108ROSSCLARKCIRCLE,DOTHAN,AL,36301,AL-Dothan,40,$2968.67,$486.59
0336-MagneticResonanceImagingandMagneticResonanceAngiographywithoutContrast,10001,SOUTHEASTALABAMAMEDICALCENTER,1108ROSSCLARKCIRCLE,DOTHAN,AL,36301,AL-Dothan,1656,$3729.89,$300.47

Thank you for your time and suggestions.

Re: proc import csv file in sas studio healthcare data

Reeza — Tue, 04 Oct 2016 03:25:51 GMT

Verify your TERMSTR with a text editor. If you can post a sample of the text file itself.

Re: proc import csv file in sas studio healthcare data

pandyat0 — Tue, 04 Oct 2016 05:29:29 GMT

is this what you mean?

APCG,ProvId,ProvName,ProvStrAd,ProvCity,ProvSt,ProvZipC,ProvHRR,OPServs,AvgEstSubChrg,AvgTotPay
0013-LevelIIDebridement&Destruction,10001,SOUTHEASTALABAMAMEDICALCENTER,1108ROSSCLARKCIRCLE,DOTHAN,AL,36301,AL-Dothan,639,$391.64,$54.94
0015-LevelIIIDebridement&Destruction,10001,SOUTHEASTALABAMAMEDICALCENTER,1108ROSSCLARKCIRCLE,DOTHAN,AL,36301,AL-Dothan,503,$595.82,$83.51
0019-LevelIExcision/Biopsy,10001,SOUTHEASTALABAMAMEDICALCENTER,1108ROSSCLARKCIRCLE,DOTHAN,AL,36301,AL-Dothan,23,$3531.88,$254.15
0020-LevelIIExcision/Biopsy,10001,SOUTHEASTALABAMAMEDICALCENTER,1108ROSSCLARKCIRCLE,DOTHAN,AL,36301,AL-Dothan,32,$4596.94,$507.30
0078-LevelIIIPulmonaryTreatment,10001,SOUTHEASTALABAMAMEDICALCENTER,1108ROSSCLARKCIRCLE,DOTHAN,AL,36301,AL-Dothan,142,$218.82,$87.60
0096-LevelIINoninvasivePhysiologicStudies,10001,SOUTHEASTALABAMAMEDICALCENTER,1108ROSSCLARKCIRCLE,DOTHAN,AL,36301,AL-Dothan,363,$988.04,$93.59
0204-LevelINerveInjections,10001,SOUTHEASTALABAMAMEDICALCENTER,1108ROSSCLARKCIRCLE,DOTHAN,AL,36301,AL-Dothan,385,$2084.46,$147.57
0206-LevelIINerveInjections,10001,SOUTHEASTALABAMAMEDICALCENTER,1108ROSSCLARKCIRCLE,DOTHAN,AL,36301,AL-Dothan,67,$1438.85,$240.75
0207-LevelIIINerveInjections,10001,SOUTHEASTALABAMAMEDICALCENTER,1108ROSSCLARKCIRCLE,DOTHAN,AL,36301,AL-Dothan,3169,$2228.79,$480.49
"0209-LevelIIExtendedEEG,Sleep,andCardiovascularStudies",10001,SOUTHEASTALABAMAMEDICALCENTER,1108ROSSCLARKCIRCLE,DOTHAN,AL,36301,AL-Dothan,729,$4468.12,$697.15
0265-LevelIDiagnosticandScreeningUltrasound,10001,SOUTHEASTALABAMAMEDICALCENTER,1108ROSSCLARKCIRCLE,DOTHAN,AL,36301,AL-Dothan,279,$805.98,$56.25
0267-LevelIIIDiagnosticandScreeningUltrasound,10001,SOUTHEASTALABAMAMEDICALCENTER,1108ROSSCLARKCIRCLE,DOTHAN,AL,36301,AL-Dothan,1737,$858.17,$134.91
0269-LevelIIEchocardiogramWithoutContrast,10001,SOUTHEASTALABAMAMEDICALCENTER,1108ROSSCLARKCIRCLE,DOTHAN,AL,36301,AL-Dothan,844,$2024.28,$339.31
0270-LevelIIIEchocardiogramWithoutContrast,10001,SOUTHEASTALABAMAMEDICALCENTER,1108ROSSCLARKCIRCLE,DOTHAN,AL,36301,AL-Dothan,40,$2968.67,$486.59
0336-MagneticResonanceImagingandMagneticResonanceAngiographywithoutContrast,10001,SOUTHEASTALABAMAMEDICALCENTER,1108ROSSCLARKCIRCLE,DOTHAN,AL,36301,AL-Dothan,1656,$3729.89,$300.47

Also, I have used this code to make it work but did not understand guessingrows? and later found out i am using Linux, so use TERMSTR=LF

FILENAME op2013"/home/pandyat0/sasuser.v94/Outpatient_2013.csv" TERMSTR=LF;
/** Import the CSV file.  **/
PROC IMPORT DATAFILE=op2013
		    OUT=WORK.op2013
		    DBMS=CSV
		    REPLACE;
		    GETNAMES=Yes;
		    guessingrows=44353;
RUN;

Let me know what you think and how I can make a choropleth map of US in gplot by using address on this file to locate each hospital.

Thanks in advance.

Re: proc import csv file in sas studio healthcare data

Reeza — Tue, 04 Oct 2016 05:54:39 GMT

GuessingRows tells SAS how many rows to read before it attempts to assign a variable type and format. It has nothing to do with your original issue, that was the TERMSTR.

Re: proc import csv file in sas studio healthcare data

pandyat0 — Fri, 14 Oct 2016 14:02:41 GMT

Hi Reeza,
Thank you for clarifying that. I later looked at the file and figure out the problem. It was with titles, I should not have space or symbols within it. So I manually opened the file in excel and rename (or deleted spaces) from titles. Then I imported file again in SAS and it worked.

Re: proc import csv file in sas studio healthcare data

pandyat0 — Fri, 14 Oct 2016 16:59:49 GMT

Hi Reeza,

I have imported the file and now I am trying to separate the first column where '-' is. And then I wanted to make new column and save the column as AmbuCode. So far, I have done this. I want to know how I can save a new column from existing column after separating it from the first column. Here is code so far.

/**Separate the codes from the Ambulatory cause. I will separate the first column in two, where the '-' is,
 so I can see codes and cause separately. **/
data WORK.op2013AmbulatoryTitleCodes;
 infile "/home/pandyat0/sasuser.v94/Outpatient_CY2013.csv";
 input AmbulatoryPaymentClassificationG $ 1-85;
 AmbulCode = compress(AmbulatoryPaymentClassificationG,'-');/* removes - from first column*/
 AmbulRemoveQuote=compress(AmbulCode, '" "'); /* Removes Quotes from 0209 code*/
 AmbCodeOnly = substr(AmbulRemoveQuote, 1, 4); /*saves first four characters as code  */
 run;
 proc sql outobs=100;
   title 'Ambulatory Codes';
   select 'Ambulatory Code is', AmbCodeOnly
      from WORK.OP2013AMBULATORYTITLECODES;
run;

The outcomes shows as Codes but I wanted that column to be its own, new column within the main dataset. How do i add that?

Re: proc import csv file in sas studio healthcare data

Reeza — Fri, 14 Oct 2016 17:44:30 GMT

Your code doesn't make a whole lot of sense? Why are you importing all the characters as a single variable?

You appear to have variable names and a delimiter of comma between fields.

Use SCAN() to separate components

x=scan(variable, 1, "-");

y=scan(variable, 2, "-");