Exporting to SAS from big excel file with 10,000 rows and 1000 columns/reorganizing

#1
Dear Friends, thanks in advance for your support and help. I have a big excel file which needs to be exported into sas. I need to reorganize the data when the SAS data set is created. To explain what I mean I have created a tiny sample of excel file in the attached file. Sheet 1 mirrors the way data looks now in excel and sheet 2 mirrors what I want it to look once exported to sas.
I could have done the reorganization in excel itself using pivot tables but that is not possible in this case as the number of rows will exceed 10 million which excel cannot support. So, I have to do the coding in SAS itself.
Since there are many such excel files which I will need to export in a similar fashion, it will be impractical to cut down each file into 10 sheets and then create pivot tables.
Please let me know if you cannot understand from the sheet attached. As i said, its just a very tiny version of real sheet which has 1000 columns of company codes and daily dates for 30 years!
 

noetsi

Fortran must die
#2
Re: Exporting to SAS from big excel file with 10,000 rows and 1000 columns/reorganizi

I would guess you need to transpose your columns and then rename the new column that results, but I don't know how to do that and keep the association with the dates you want.
 

hlsmith

Less is more. Stay pure. Stay poor.
#3
Re: Exporting to SAS from big excel file with 10,000 rows and 1000 columns/reorganizi

At first I was thinking proc transpose, then noticing noetsi post it really seemed like: proc import, then array in a data step, then proc sort. Going from short form to long form. Seems like for these you typically need an ID number, however yours appears to be a date. I don't know if that will pose a problem for you. Data management is not my forte.
 

noetsi

Fortran must die
#4
Re: Exporting to SAS from big excel file with 10,000 rows and 1000 columns/reorganizi

If you just wanted to collapse many columns into one I think transpose would work. But you want to associate (continue to associate) those columns with a specific date and I am not sure transpose does that.
 

Mean Joe

TS Contributor
#5
Re: Exporting to SAS from big excel file with 10,000 rows and 1000 columns/reorganizi

Take your dataset with 5 columns (+ Date column) and make 5 datasets.
Then append the datasets using SET statement.

Code:
*Take your dataset with 5 columns (+ Date column) and make 5 datasets;
data col1(keep=Date IN_MNM) col2(keep=Date IN_CEC) col3(keep=Date IN_3II) col4(keep=Date IN_3MI) col5(keep=Date IN_EOI);
  set Sheet1;
run;

*Your 5 datasets have diff column names -- before appending must make them have common column names;
data col1_setup;
  set col1;
  Company_Code="IN:MNM";
  rename IN_MNM=PH_Value;
run;
data col2_setup;
  set col2;
  Company_Code="IN:CEC";
  rename IN_CEC=PH_Value;
run;
*etc;

data sheet2;
  set col1_setup col2_setup col3_setup col4_setup col5_setup;
run;