BookmarkSubscribeRSS Feed
Rookie_123
Fluorite | Level 6

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 

4 REPLIES 4
Kurt_Bremser
Super User

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.

Rookie_123
Fluorite | Level 6
Can you please show a sample code of datastep to change variable format, informat, length, type
Kurt_Bremser
Super User

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.

ballardw
Super User

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 29221 views
  • 2 likes
  • 3 in conversation