topic Re: Changing variable Format with proc import in SAS Programming

Changing variable Format with proc import

Rookie_123 — Thu, 16 Aug 2018 16:54:59 GMT

TLDR:

How to specify column format, informat, lenght, type in PROC IMPORT.My database is in xlsx format

Longer Version

I am importing my 3 excel databases A, B, C PROC IMPORT. I want to append this datasets after importing.

These databases consists of date variables in different columns

Database A will have values in column DateA

Database B will have values in column DateB

Database C will have values in column DateC

All the three database will have columns DateA, DateB, DateC.But values in those columns will depend on what database it is.

So when I import Database A the column type of DateB and DateC is set to character since they dont have values in them.How can I specify at the import itself that DateB and DateC should be considered as Date type.

Another problem is the lenghts, format, informat of columns is set as it is in Database A.Due to which when I try to append B with A it gives me a truncation warning.I dont want my data to be truncated.

I have in all 50 columns so manually specifying each columns lenght, format, informat etc. wont be a feasible thing.Is there any better alternative

Re: Changing variable Format with proc import

Kurt_Bremser — Thu, 16 Aug 2018 18:03:52 GMT

If you want to have control over variable attributes, forget the Excel file format. Importing it always involves guessing on the part of SAS.

If you want to have control, save your data to csv files and read those with a data step.

Re: Changing variable Format with proc import

Rookie_123 — Thu, 16 Aug 2018 18:06:54 GMT

Can you please show a sample code of datastep to change variable format, informat, length, type

Re: Changing variable Format with proc import

Kurt_Bremser — Thu, 16 Aug 2018 18:25:11 GMT

You do not run a data step to change anything, you read data from a file format that can be reliably used.

You can use proc import once for the csv file, then copy the data step from the log, and adapt it to your needs.

Re: Changing variable Format with proc import

ballardw — Thu, 16 Aug 2018 19:39:16 GMT

@Rookie_123 wrote:
Can you please show a sample code of datastep to change variable format, informat, length, type

If you run Proc Import on a CSV file the log will contain an example. That was how I generated the skeleton for this code which I modified slightly by changing the lengths of some variables to match the length of counterparts in another data set.

Not changing anything, reading as needed:

data lib.AddressRaw    ;
   infile Siradd delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
   informat School_Name $80. ;
   informat Type $8. ;
   informat Title $16. ;
   informat First_Name $17. ;
   informat Last_Name $15. ;
   informat Phone $12. ;
   informat Fax $12. ;
   informat Email $44. ;
   informat District_Code $15. ;
   informat District $26. ;
   informat Address $32. ;
   informat City $17. ;
   informat County $10. ;
   informat State $2. ;
   informat Zip $5. ;
   informat Principal_Title $35. ;
   informat Principal_First_Name $14. ;
   informat Principal_Last_Name $14. ;
   input
      School_Name $
      Type $
      Title $
      First_Name $
      Last_Name $
      Phone $
      Fax $
      Email $
      District_Code $
      District $
      Address $
      City $
      County $
      State $
      Zip $
      Principal_Title $
      Principal_First_Name $
      Principal_Last_Name $
   ;
run;

The SIRADD is a file reference pointing to a CSV file. In this case I wanted everything to be character so things like district_code or ZIP that have only digits involved are read as character since I don't do any calculations on those.

An informat used in this manner will set the variable type and length. I had no need to assign any specific format to these values but could add a FORMAT statement if that were needed.