topic Re: How to transpose data, with the missing id/code? in SAS Enterprise Guide

How to transpose data, with the missing id/code?

EllaXu — Thu, 16 Jul 2020 11:59:48 GMT

Hi, there,

Can anyone please show me how to transpose data with missing ID /CODE? Please see the sample enclosed.

See the screenshot below:

C:\Users\22296171\Desktop

Re: How to transpose data, with the missing id/code?

Jagadishkatam — Thu, 16 Jul 2020 12:09:42 GMT

We cannot transpose the missing values

Re: How to transpose data, with the missing id/code?

EllaXu — Thu, 16 Jul 2020 12:15:36 GMT

Sorry I didn't describe the issue clearly. The variables are not lined up properly in the right columns(see the highlighted in yellow cells), due to the missing id/code, so the question is how can I fix the missing code or ID?

Re: How to transpose data, with the missing id/code?

andreas_lds — Thu, 16 Jul 2020 12:17:07 GMT

I can't open excel-files, so could you please post the data as data-step using datalines-statement? See https://blogs.sas.com/content/sastraining/2016/03/11/jedi-sas-tricks-data-to-data-step-macro/ for details.

Re: How to transpose data, with the missing id/code?

Kurt_Bremser — Thu, 16 Jul 2020 12:44:25 GMT

Please post your dataset in a usable way (data step with datalines), and the code you used for the transpose.

I cleaned up your Excel file, and ran this:

proc import
  datafile="/folders/myfolders/transpose1.xlsx"
  out=transpose1
  dbms=xlsx
  replace
;
run;

proc transpose data=transpose1 out=trans;
var year:;
by code notsorted;
id datatype;
run;

which worked for me.