Can you help me transforming a data set enclosed?

Reply
N/A
Posts: 1

Can you help me transforming a data set enclosed?

Hello Everyone,

I am attaching an Excel sheet with two worksheets. The first worksheet has the original data (Original-data). The second worksheet (New-data) has the transformed data sheet that I want for processing. How do I convert them? Can any body help me in writing a program?

Please note that not all countries have data for all years or for all variables. The new data set must have all countries, all years, and all variables, but there will be missing data in the appropriate places.

You can also reply me at waheed@tamucc.edu.

Thank you for your help in advance. Have a good day!

Waheed

SAS Super FREQ
Posts: 683

Re: Can you help me transforming a data set enclosed?

hi Waheed

try this code below, it will give you what is in the orignal data. to have all the countries and all the years and all the variables you first have to create a table that has all the country/year combinations as well as all the variables and then merge this with the data below

options
 
validvarname=v7
;

proc import
 
datafile="c:\temp\waheed_trialdata.xls"
 
dbms=xls
 
out=work.waheed_trialdata
 
replace
;
run;

proc sort data=work.waheed_trialdata;
  by country;
run;

proc transpose data=waheed_trialdata
 
out=work.waheed_trialdata_trsp
 
name=source
 
label=label
;
  by country;
  id variable;
  *
  * take any var that starts with "_"
  *;

 
var _:;
run;

Bruno
PROC Star
Posts: 7,363

Re: Can you help me transforming a data set enclosed?

Like Bruno suggested, proc transpose requires the least coding to accomplish what you want.  Conversely, it can also be done in a datastep (Note: when I imported your spreadsheet the year variables imported as _000 to _011):

data want (keep=country year v1-v4);

  set have;

  by country;

  array thedata(12) _000-_011;

  array years(4,12);

  retain years;

  if first.country then do;

    call missing(of years(*));

  end;

  do i=1 to 12;

    years(input(substr(variable,2),1.),i)=thedata(i);

  end;

  if last.country then do;

    do i=1 to 12;

      year=i+1999;

      v1=years(1,i);

      v2=years(2,i);

      v3=years(3,i);

      v4=years(4,i);

      output;

    end;

  end;

run;

New Contributor
Posts: 3

Re: Can you help me transforming a data set enclosed?

Both approaches are fine and will work. However the data step approach can become much more efficient in the following circumstances.

Case 1, if the initial file is very large and is possibly stored on cartridge / tape, and I am talking a few hundred million records or more.

In such a case it is possible that proc transpose may not work due to the machine memory limitations.

The sort process may become time consuming and the sort may not be needed later

If the input file is on cartridge then  output may require large disk space or back to cartridge / tape which can slow the process

Case 2. Sparsely populated datasets

The second step allows the user to control what goes into the transposed file. So if the base data was sparsely populated with a large number of nulls or zeroes which were to be deleted later on, then the data step process can eliminate them with a simple IF statement before the output reducing the size of the dataset.


Ask a Question
Discussion stats
  • 3 replies
  • 248 views
  • 6 likes
  • 4 in conversation