Proc Transpose

Nisser — Tue, 11 Jan 2011 04:31:14 GMT

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;

Re: Proc Transpose

Peter_C — Tue, 11 Jan 2011 11:57:14 GMT

> 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

Re: Proc Transpose

MikeZdeb — Fri, 14 Jan 2011 03:29:03 GMT

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

topic Re: Proc Transpose in SAS Programming

Proc Transpose

Re: Proc Transpose

Re: Proc Transpose