topic Re: Importing quarterly data into SAS in SAS Procedures

Importing quarterly data into SAS

GARYV — Mon, 02 Apr 2018 20:06:05 GMT

Hello,

I have data from each quarter of a year in a fixed width text file. I am using the import function and then after selecting required variables, exporting it as an excel ( tried different formats too) file so that I can combine all four quarters of data for a particular year. But, when I tried to merge them using SAS its not working because for many variables SAS is reading them differently, either as numeric or character value. Each quarter has 300 plus variables and around 80,000 rows. How can I use SAS to read variable similarly for all quarters so that I can merge them? Thank you.

Re: Importing quarterly data into SAS

Kurt_Bremser — Mon, 02 Apr 2018 20:15:07 GMT

DO NOT use proc import repeatedly. Once proc import has imported a text file, you find a data step in the log. Copy that, and use it for all similar files. Proc import is a guessing procedure that will deliver different structures for different content.

Re: Importing quarterly data into SAS

ballardw — Mon, 02 Apr 2018 21:44:50 GMT

I would be very surprised to find that Proc Import read any fixed column file correctly as data can appear such as:

1234567234567

and be the values for 4 or 5 variables AND include implied decimals.

You should have a document that describes what columns represent each variable and whether it is expected to be character, numeric or a date/time/datetime variable. Likely it also describes the expected appearance for such things as dates and whether special codes are used to indicate missing data (old school values like -99 or -999 or 9999999 ).

The use that document to write a data step to read one of the files. Once you have the code to read one file you change the input file name and the output data set to read each.

Generic example:

data firstquarter;
   infile "path/filename"   ;
   input
      var1  1-8
      var2 $ 9-15
      var3  16-17
      var4  18-25
   ;
run;

reads four variables var1, var3 and var4 are numeric, var2 is character (the $). The numbers are the start/end columns of a variable and should be in your documentation. If a variable only takes a single position you use Varx 234 for example to read a single digit from column 234.

Dates and times likely will involve specifying the INFORMAT to read them correctly and assign appropriate formats for display.

In some cases the documentation is actually amenable to copy and paste into the Input statement if it reads something like

Variable name start column end column type

identifier 1 20 character

value 21 25 numeric

if you can paste this into a spreadsheet with the values in the correct columns you can actual write a formula to build

"indentifier $ 1 - 20"

"value 21 - 25"

using the type information.

If you have special code values in variables that should be numeric but look like N/A for "not available" or similar you will get missing (usually appropriate) but if the values are 9999999 to indicate missing or such you may either want to create custom informats to use.

Then either use a data step or proc append to combine the data in the order you want.

Re: Importing quarterly data into SAS

GARYV — Mon, 30 Apr 2018 19:27:04 GMT

Hi Kurt,

I have another question. Which one is better to use in data steps, 'Missover' or 'Truncover'? Proc import data log has missover but when I checked for the missover vs truncover comparison, I felt latter is better to use. Any advice? Thank you

Mine is a healthcare data. Attached is the link to the data source.

https://www.dshs.texas.gov/THCIC/Hospitals/Download.shtm

Re: Importing quarterly data into SAS

Kurt_Bremser — Mon, 30 Apr 2018 19:42:26 GMT

Truncover is better, hands down. Missover is more or less legacy.

Re: Importing quarterly data into SAS

GARYV — Mon, 30 Apr 2018 19:47:01 GMT

Thank you