BookmarkSubscribeRSS Feed
Nisser
Calcite | Level 5
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;
2 REPLIES 2
Peter_C
Rhodochrosite | Level 12
> 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
MikeZdeb
Rhodochrosite | Level 12
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 761 views
  • 0 likes
  • 3 in conversation