topic Re: Need to import tabular type report in SAS in SAS Programming

Need to import tabular type report in SAS

nishSAS — Wed, 08 Aug 2018 13:52:01 GMT

Hello Everyone,

Kindly help me to import the tabular type report into SAS data set. Once that report gets import into SAS then i want to transpose that data set with regular data form.

I have attached excel file for your reference as i have "Table 1" data which i want to convert into "Table 2" form in SAS.

Hope this description helps you to understand my issue.

Thanks,

Nishant Paranjpe

Re: Need to import tabular type report in SAS

ballardw — Wed, 08 Aug 2018 14:33:02 GMT

First thing, is your actual report file in Excel or something else? If something else then provide that document, not Excel.

If the files are in Excel have you installed the SAS PCfiles server? that may be the easiest way.

Second thing, is that example table one exactly like that or does it have different numbers of columns and with different "date" column headers.

I believe it will be easier to read into a the normalized form instead of reading and then transposing because the column headers you have, in typical Excel user fashion, are not valid for SAS variable names and will be ugly; the multiple sections of "table" information are going to require parsing in the first place as you do not want the blank lines or the total rows.

Re: Need to import tabular type report in SAS

nishSAS — Wed, 08 Aug 2018 14:52:57 GMT

Yes my data is in excel format and whatever data i have provided in the example file is as it is what i have

Re: Need to import tabular type report in SAS

ballardw — Wed, 08 Aug 2018 15:48:11 GMT

@nishSAS wrote:

Yes my data is in excel format and whatever data i have provided in the example file is as it is what i have

Actually if you added the header Normalization then you modified the data to a different format.

Also your Table 2 modified the data. So your posted example is not what you actually have. You should post an example as it actually exists.

Also your example output has two column headers "metric" which is not acceptable for SAS data sets.

Re: Need to import tabular type report in SAS

ballardw — Wed, 08 Aug 2018 16:21:24 GMT

Something like this may get you started.

First step: save the Excel as CSV file format. That allows use of the INPUT statement which is a tad more flexible than working with Excel files in any of the raw forms. Change the FILENAME statement to point to the location and name of your file. You could also change the name of the data set to something more descriptive than read if you want to.

/* save the excel as CSV*/
filename  ex 'X:\data\example.csv';

data read;
  infile ex dlm=',' dsd truncover;
  /*read a line and hold for parsing*/
  informat Product_id Product_str Metric $15.;
  /* this will hold the date value column headings
     if there may be more than 10 OR always less than
     10 set the value to the maximun number expected
  */
  array d{10};
  informat d1 - d10 mmddyy10.;
  format d1-d10 mmddyy10.;
  informat factor best8.;
  format MetricDate mmddyy10. factor best8.;
  retain d1-d10;
  input Product_id Product_str Metric @;

   /* if first column is blank or Table 1 skip*/
   if Product_id in ("" "Table 1") then do;
      /* reset date to missing*/
      call missing( of d(*));
      input; 
   end;
   else if Product_id = 'Product ID' then do;
      input d1 - d10;
      /* read each of the column headings into a date value*/
      input;
   end;
   else do;
   /*These should be the actual values wanted*/
      do i=1 to dim(d);
         if not missing( d[i]) then do;
            MetricDate= d[i];
            input factor ?? @;
            output;
         end;
      end;
   end;
   keep Product_id Product_str Metric MetricDate factor;
run;

Re: Need to import tabular type report in SAS

Vince_SAS — Wed, 08 Aug 2018 22:24:46 GMT

What version of SAS are you using, and on what operating system is it running on? Do you license SAS/ACCESS to PC File Formats? Do you run SAS using SAS Enterprise Guide or some other way?

I made a small change to your Excel workbook and added 3 named ranges - table1, table2, and table3 - to identify each of the three tables. This assists with the import to SAS. I attached it as "Example_Vince.xlsx".

Here is sample code (SAS running on Windows) to import the 3 tables into SAS:

options validvarname=any validmemname=extend;

libname xl pcfiles path='Example_Vince.xlsx';

proc datasets lib=xl;
  copy in=xl out=work;
    select table1 table2 table3;
run; quit;

title 'Top Left Table';
proc print data=work.table1; run; quit;

title 'Bottom Left Table';
proc print data=work.table2; run; quit;

title 'Right Table';
proc print data=work.table3; run; quit;

Vince DelGobbo

SAS R&D

Re: Need to import tabular type report in SAS

nishSAS — Thu, 09 Aug 2018 10:16:30 GMT

Thanks Vince,

I am using SAS9.4 version on 32bit OS

In your attached file i did not find three tables. there are only two tables showing which i provided.

Re: Need to import tabular type report in SAS

Vince_SAS — Thu, 09 Aug 2018 12:34:02 GMT

Are you using the 32-bit Windows operating system?

Do you license SAS/ACCESS to PC File Formats?

Do you run SAS using SAS Enterprise Guide or some other way?

For the purpose of importing, I added 3 named ranges for the 3 rectangular areas that had column headings. There are two small rectangular areas on the right side, and one large one on the left. The import process handles each of these separately.

Importing to SAS works best when the objects that you are trying to import resemble SAS tables.

Vince DelGobbo

SAS R&D