topic Re: Reorganizing Data in SAS Data Management

Reorganizing Data

NickA1 — Wed, 10 Jul 2019 15:11:19 GMT

Hi, I'm a new user and I am trying to do some data restructuring. I am ultimately putting data in Power BI to create some charts. My data is in excel, but the file is so massive that it takes a very long time to do anything because it spends so much time buffering. Basically what I want to do is take matrix style data, and stack each column on top of the others.

So, I have a date column, and then columns for different stocks that contain the prices of each of those stocks on each of the days in the date column. To be able to use this is Power BI, I need to have all of the prices stacked on top of each other, and then I need to add another column that has what the stock is. So, all in all I need three columns: Date, Stock Identifier, and Prices.

Does anyone have any idea on how I can do this in SAS?

Re: Reorganizing Data

PaigeMiller — Wed, 10 Jul 2019 15:34:00 GMT

Please show us a portion of your Excel file, so we have a better idea of what the data looks like.

I show example SAS code in which there are only three stocks, and the data is re-arranged as I think you have asked:

data a;
    input date :date7. ford chevy gm;
	cards;
01JAN18 203 209 18
02JAN18 204 210 19
;
data want;
	set a;
	array stocks ford--gm;
	do i=1 to dim(stocks);
	    value=stocks(i);
		stockname=vname(stocks(i));
		output;
	end;
	keep date value stockname;
	format date date7.;
run;

In your case, your input data set would be from Excel and not from data set A.

Re: Reorganizing Data

Reeza — Wed, 10 Jul 2019 15:22:00 GMT

PROC TRANSPOSE most likely.

Re: Reorganizing Data

NickA1 — Wed, 10 Jul 2019 15:42:14 GMT

Here is some of the data

Re: Reorganizing Data

Reeza — Wed, 10 Jul 2019 15:57:42 GMT

https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-wide-to-long-using-proc-transpose/

Re: Reorganizing Data

Tom — Wed, 10 Jul 2019 16:29:30 GMT

@NickA1 wrote:

Here is some of the data

The file you posted is too large for the web site to show us:

Sheet "Sheet2" is too large to be previewed. Please download the file to view this sheet.

Post just a subset of the rows and columns so we get an idea of how the data is structured. Even better post a simple SAS data step that creates a small sample of what you get after importing the XLSX file into SAS.

Re: Reorganizing Data

NickA1 — Wed, 10 Jul 2019 17:22:40 GMT

here is a new version of the file

Re: Reorganizing Data

Reeza — Wed, 10 Jul 2019 17:24:52 GMT

Did you check the link I posted, it has the solution you need in there? If you tried and it didn't work, post your code.

Re: Reorganizing Data

Tom — Wed, 10 Jul 2019 17:28:40 GMT

SAS should be able to read in the Excel file using PROC IMPORT or XLSX libname engine.

Since it looks like you just have numeric variables your code to transform it could be as simple as:

proc transpose data=HAVE out=WANT;
  by date;
run;

since by default it will transpose all of the numeric variables.

You will then get a dataset with DATE, _NAME_ and COL1. You might also have _LABEL_ if the any of the variables had a label attached.