DATA Step, Macro, Functions and more

how to transpose data in the same excel sheet

Reply
New Contributor
Posts: 4

how to transpose data in the same excel sheet

In my work, I need transpose the data from one excel sheet,using MACRO ,but the sheet have many different subforms.

The orignal data is like this:

IDNameW5W6W7W8W9W10W11
1001A112141111111213
1002A213141212121314
         
IDNameY1Y2Y3Y4Y5Y6Y7
2001B115161515151617
         
IDNameM3M4M5M6M7M8M9
3001C111121314151617

 

and I need the results like this:

 

IDNameTimeCount
1001A1W911
1001A1W1012
1001A1W1113
1002A2W513
1002A2W912
1002A2W1013
1002A2W1114
2001B1Y616
2001B1Y717
3001C1M715
3001C1M816
3001C1M917

 

Cound someone help me? Thanks.

PROC Star
Posts: 1,218

Re: how to transpose data in the same excel sheet

So are the three different data sets?

 

And why do you only want to keep M7-M9 for C1 eg?

New Contributor
Posts: 4

Re: how to transpose data in the same excel sheet

I just show the part resuts and there are W12 Y8 Y9 data etc.
Super User
Super User
Posts: 9,441

Re: how to transpose data in the same excel sheet

First off your original data is the one that is transposed, your want data is normalised.  

Secondly, where have you got the "using MACRO" part from?  There is no need to use macro, ever.  It has its place, but fundamentally it is a find replace text generator.  What you want can be done simply in datasteps or procedures in Base SAS - which is the programming language.

Thirdly, you say you want to transpose data in the same Excel file, you would not do this in SAS, in SAS you read the data, process the data, then write the data out to a file.  You can read the data in a number of ways depending on what SAS version you are using, what Excel version you are using etc. of which you have not mentioned anything.  I am not going over every possible connotation of the two.

 

To be honest, looking at that dreadful format of data, I would probably - if it was me - invest some time in Office VBA within Excel itself and process the data, drop the SAS part totally.  Should be pretty straight-forward, just a matter of selecting range.start to end, then looping over to the right each observation and copying to a new worksheet.

 

Another option is to read the whole sheet into one dataset in SAS, then in SAS write a datastep which goes over each of the columns and outputs the results, maybe something like:

 

data want (keep=id name time count);
  set have;
  array col{7};
  array heads{7};
  if col{1}="" then delete;
/* Headers */
  if char(col{i},1) in ("W","Y","M") then do;
    do j=1 to 7;
      heads{j}=col{j};
    end;
  end;
/* Data */
  else do;
    do j=1 to 7;
      time=heads{j};
      count=col{j};
      output;
  end;
run;     

 

 

New Contributor
Posts: 4

Re: how to transpose data in the same excel sheet

Thanks a lot. But,  actually, the data in the excel sheet is part of all data , W11-W15 Y8-Y20 and the col is not 7.

Aslo the ID and Name is partly:

 

IDNameY1Y2Y3Y4Y5Y6Y7Y8Y9
5008E1151615151516171819
5009E2151615151516171819
5010E3151615151516171819

 

The excel sheet have large data like this. could you tell me how to use a macro to process the data?

Super User
Super User
Posts: 9,441

Re: how to transpose data in the same excel sheet

"Thanks a lot. But,  actually, the data in the excel sheet is part of all data ," - by this you mean there are other data included in the sheet/workbook?  If so then you have problems, Excel is not a database, refer back to the actual data.

 

"and the col is not 7." - the number 7 was derived from what you posted, increase this per the data that only you can see, it refers to the number of elements in the imported data.  E.g. if after a proc import you have col1 col2 col3, then col{3} indicates 3 elements all called col.  In fact you would need to skip the first two as they would reference id/name.

 

"The excel sheet have large data like this. could you tell me how to use a macro to process the data?" - I am afraid you don't understand something here.  In SAS macro are not there for processing data, they are a find and replace text generator - so of no help in this case.  In terms of VBA macros then this is outside the scope of a SAS forum, perhaps something along these lines:

https://www.get-digital-help.com/2012/05/07/vba-macro-normalize-data/

New Contributor
Posts: 4

Re: how to transpose data in the same excel sheet

Thanks. I mean I need use macro to process many excel sheets. The data is large and I need transpose the data to do the next work.

Super User
Posts: 23,332

Re: how to transpose data in the same excel sheet

Are you looking for an Excel VBA Macro? Or a SAS Macro?

 

How is Excel involved here? Are you using SAS Add In for Office within Excel? Or just using terminology you're familiar with.

 

Regardless your full process is likely something of the sort, my assumptions of course:

 

1. Import data into SAS

2. Transpose/reformat

3. Export back to Excel 

4. Repeat #1 for lots of sheets or files

 

If this is correct, your first step is to get it working for one iteration first without macros. Once you have that working, then you can move on to automation. You start by first determine what needs to change in each iteration, by doing it once or twice via copy/paste/change and identifying which parts need to be dynamic. Then you can start writing a macro.

 

If any of the above is accurate, please indicate where in this process you currently are working on and what specifically you're having issues with.

 


dlinda wrote:

Thanks. I mean I need use macro to process many excel sheets. The data is large and I need transpose the data to do the next work.


 

Ask a Question
Discussion stats
  • 7 replies
  • 87 views
  • 0 likes
  • 4 in conversation