DATA Step, Macro, Functions and more

Proc Transpose

Reply
New Contributor
Posts: 3

Proc Transpose

Hi everyone,

We have a large data with multiple observations in rows.

We wanted to transpose a column, CMMED_NEW (Variable) with several entries for same ID at different visits levels (visits is coded VISCODE_NEW, 1 to 12 visits codes. NOTE: number of visits for each ID differ. Maximum visits 10, minimum 1).

We used the syntax below to transpose the Column CMMED_NEW but always got error message that there was "no ID variable in the Data" even thought the ID variable in in the file.

Can anyone kindly advice.

Many thanks in advance.

Nisser

Syntax used:
Data WORK.CLINICAL_test;
Set WORK.CLINICAL;
Proc Sort Data=WORK.CLINICAL_test;
by ID RID VISCODE_NEW CMMED_NEW;
Run;

Data WORK.CLINICAL_testss;
Set WORK.CLINICALS_test;

Array AA(12) EXAMDATE1 - EXAMDATE12;
Array BB(12) CMMED_new1 - CMMED_new12;
Array CC(12) CMREASON1 - CMREASON12;
Array DD(12) CMBGN1 - CMBGN12;
Array EE(12) CMCONT1 - CMCONT12;
Array FF(12) CMEND1 - CMEND12;
Array GG(12) CMEND_Revised1 - CMEND_Revised12;
Array HH(12) Hypertension_Hx1 - Hypertension_Hx12;
Array II(12) HTN_Med_YN1 - HTN_Med_YN12;
Array JJ(12) HTN_Med_YNN1 - HTN_Med_YNN12;
Array KK(12) HTN_Med_YNNN1 - HTN_Med_YNNN12;
Array LL(12) Med_Duration_days1 - Med_Duration_days12;
Array MM(12) HTN_ACE1 - HTN_ACE12;
Array NN(12) HTN_ARB1 - HTN_ARB12;
Array OO(12) HTN_Bblocker1 - HTN_Bblocker12;
Array PP(12) HTN_CCBlocker1 - HTN_CCBlocker12;
Array QQ(12) HTN_Diuretic1 - HTN_Diuretic12;
Array RR(12) HTN_Alpha_Block1 - HTN_Alpha_Block12;
Array SS(12) HTN_Vasodil1 - HTN_Vasodil12;
Array TT(12) HTN_Cent_Act1 - HTN_Cent_Act12;
Array UU(12) HTN_Others1 - HTN_Others12;
Array VV(12) HTN_Med_Any1 - HTN_Med_Any12;
Array WW(12) Dementia_YN1 - Dementia_YN12;
Array XX(12) Dementia_Med1 - Dementia_Med12;
Array YY(12) Vitamins_E1 - Vitamins_E12;
Array ZZ(12) Vit_B12_Folic1 - Vit_B12_Folic12;
Array AB(12) Vitamins_DHA1 - Vitamins_DHA12;
Array AC(12) Vitamins_Others1 - Vitamins_Others12;

Do VISCODE_new=1 to 12;
Set WORK.CLINICAL_RECCMEDS_testss; *WORK.CLINICAL_RECCMEDS_test3;
If VISCODE_New ^=".";
By ID;

AA(VISCODE_NEW)=EXAMDATE;
BB(VISCODE_NEW)=CMMED_new;
CC(VISCODE_NEW)=CMREASON;
DD(VISCODE_NEW)=CMBGN;
EE(VISCODE_NEW)=CMCONT;
FF(VISCODE_NEW)=CMEND;
GG(VISCODE_NEW)=CMEND_Revised;
HH(VISCODE_NEW)=Hypertension_Hx;
II(VISCODE_NEW)=HTN_Med_YN;
JJ(VISCODE_NEW)=HTN_Med_YNN;
KK(VISCODE_NEW)=HTN_Med_YNNN;
LL(VISCODE_NEW)=Med_Duration_days;
MM(VISCODE_NEW)=HTN_ACE;
NN(VISCODE_NEW)=HTN_ARB;
OO(VISCODE_NEW)=HTN_Bblocker;
PP(VISCODE_NEW)=HTN_CCBlocker;
QQ(VISCODE_NEW)=HTN_Diuretic;
RR(VISCODE_NEW)=HTN_Alpha_Block;
SS(VISCODE_NEW)=HTN_Vasodil;
TT(VISCODE_NEW)=HTN_Cent_Act;
UU(VISCODE_NEW)=HTN_Others;
VV(VISCODE_NEW)=HTN_Med_Any;
WW(VISCODE_NEW)=Dementia_YN;
XX(VISCODE_NEW)=Dementia_Med;
YY(VISCODE_NEW)=Vitamins_E;
ZZ(VISCODE_NEW)=Vit_B12_Folic;
AB(VISCODE_NEW)=Vitamins_DHA;
AC(VISCODE_NEW)=Vitamins_Others;
If last.VISCODE_New then return;
End;
Run;
Valued Guide
Posts: 2,175

Re: Proc Transpose

> Hi

> We used the syntax below

> "no ID variable in the Data" even thought the ID
> variable in in the file.

> Nisser


selected statements from your code

> Set WORK.CLINICALS_test;

> Set WORK.CLINICAL_RECCMEDS_testss;

> By ID;

You may not have posted the code which created the message, because I see the output from the sort statement "Proc Sort Data=WORK.CLINICAL_test;"
is not read by the data step causing trouble.
That is why we recommend posting the SASlog of the steps up to the point where the errors are reported.

hth
peterC
Valued Guide
Posts: 765

Re: Proc Transpose

hi ... another suggestion ... at the start of the job, instead of this ...

Data WORK.CLINICAL_test;
Set WORK.CLINICAL;

Proc Sort Data=WORK.CLINICAL_test;
by ID RID VISCODE_NEW CMMED_NEW;
Run;

how about this ...

Proc Sort data=work.clinical out=WORK.CLINICAL_test;
by ID RID VISCODE_NEW CMMED_NEW;
Run;

it eliminates an unnecessary pass through the data in that data step
Ask a Question
Discussion stats
  • 2 replies
  • 119 views
  • 0 likes
  • 3 in conversation