Converting single-quoted values in CSV to numeric with leading zeros in SAS

Accepted Solution Solved
Reply
Contributor
Posts: 61
Accepted Solution

Converting single-quoted values in CSV to numeric with leading zeros in SAS

Hi,

I'd like to convert a character variable to numeric while preserving its leading zero. My raw data are in the CSV format. Each CSV file has been converted to a SAS data set using PROC IMPORT. In the CSV files, values in the column HOSPID are put in single quotes in order to preserve their leading zeros. All these values are 5 digit long (e.g. '01201' ). Single quotes are automatically removed after CSVs are converted to .sas7bat, however, its variable attribute shows that it 7 in its length, $7. in its format and $7. in its informat. I would like to convert this variable to numeric Z5. format so it is consistent with the key variable in other data sets. After some conversion, it remains character. I am not sure if it is appropriate to use PRCO IMPORT. My code has been pasted as the following:

The CSV files have been converted to separated SAS data sets with the following macro:

%macro readCSV(outfile_prefix=,outfile_suffix=);

    %let dirname = %str(\\LEECC-PC\national_inpatient_sample\NIS_raw_data\NIS_cost_to_charge_ratio_files) ;

    %let infile_list= cc2004NIS cc2005NIS cc2006NIS cc2007NIS cc2008NIS cc2009NIS_V2 cc2010NIS cc2011NIS cc2012NIS ;

        %let n = %sysfunc(countw(&infile_list.)) ;

            %do i= 1 %to &n. ;

                %let file = %scan(&infile_list., &i.) ;

                %let year= %substr(&file.,3,4 );      /*extract year for use as file namess*/

                    proc import out=CCR.&outfile_prefix._&year._&outfile_suffix.

                            datafile= "&dirname.\&file..csv"

                            dbms=csv replace ;

                        getnames=yes;

                        datarow=2;

                    run;

            %end;   

%mend readCSV;    

%readCSV(outfile_prefix=NIS, outfile_suffix=CCR);

I then used the following code to convert character HOSPID to numeric HOSPID2:

data test;   

    set CCR.Nis_2004_CCR;

    hospid2= put(input(compress(hospid), 5.), Z5.);   

run;

The variable attribute shows that HOSPID2 has 5 in its length, $5. in its format and $5. in its informat.

I have uploaded the raw data to my website. Please download the CSV (CCR2004)  at the bottom of the following

Converting single-quoted values in CSV to numeric with leading zeros in SAS | Like-Minded Data Analy...


Accepted Solutions
Solution
‎08-16-2015 09:13 AM
Super User
Super User
Posts: 6,842

Re: Converting single-quoted values in CSV to numeric with leading zeros in SAS

A PUT() statement always generates a character string. You can use INPUT (or INPUTN) to generate a number.

data test;

  set CCR.Nis_2004_CCR;

  hospid2= input(strip(hospid), 5.);

  format hospid2 Z5.;

run;

View solution in original post


All Replies
Super User
Super User
Posts: 6,842

Re: Converting single-quoted values in CSV to numeric with leading zeros in SAS

If you know the structure of your file why would you use PROC IMPORT?  That tool can only guess at the structure of that data since a CSV file has no way to represent the metadata needed to define the structure.

data want ;

  length HOSPID YEAR 8 Z013 $2 GAPICC APICC WI_X NIS_STRATUM HOSP_REGION 8 ;

  infile 'http://likecoded.com/wp-content/uploads/2015/08/cc2004NIS3.csv' url truncover firstobs=2 dsd ;

  input hospid -- hosp_region ;

  format hospid Z5.;

run;

Contributor
Posts: 61

Re: Converting single-quoted values in CSV to numeric with leading zeros in SAS

hi Tom,

Thanks a lot for the code and advice. Data elements vary slightly from year to year in my CSV files. Is it still appropriate to read them into SAS using your method? In your INPUT statement, it seems that it can be as simple as "first-variable -- last-variable." This can be very useful when these two are consistent in all the files to read into SAS. But that's not the case in my data files. That's why I ended up with PROC IMPORT.

Cheers,

Chang

Super User
Super User
Posts: 6,842

Re: Converting single-quoted values in CSV to numeric with leading zeros in SAS

To make that program all I did was copy the column names from the first line of the CSV file and use it to create the LENGTH statement.

So it is not that hard to adapt to a new file.

You can also just use PROC IMPORT in an interactive SAS session and then recall the code that it generates and modify that to fix issue with the auto-detect types and lengths.

Solution
‎08-16-2015 09:13 AM
Super User
Super User
Posts: 6,842

Re: Converting single-quoted values in CSV to numeric with leading zeros in SAS

A PUT() statement always generates a character string. You can use INPUT (or INPUTN) to generate a number.

data test;

  set CCR.Nis_2004_CCR;

  hospid2= input(strip(hospid), 5.);

  format hospid2 Z5.;

run;

🔒 This topic is solved and locked.

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

Discussion stats
  • 4 replies
  • 286 views
  • 0 likes
  • 2 in conversation