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

[EllaXu]

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 

[Jagadishkatam]

We cannot transpose the missing values

[EllaXu]

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?

[andreas_lds]

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.

[Kurt_Bremser]

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.