Okay. I have been racking my brain over this all day. I need some insight here.
For simplicity, I have a table with a list of subjects and the visits for each subject. I have another table of timepoints that lists the timepoints for each specific visits:
data timepoints;
length visitnum 8 ptpt $12 egnum 8;
input visitnum ptpt $ egnum;
cards;
30001 PREDOSE 0.1
30001 PREDOSE 0.2
30001 PREDOSE 0.3
30001 4HR_POSTDOSE 4.0
30015 PREDOSE 0.0
30015 4HR_POSTDOSE 4.0
;
run;
data subj;
length subjid $11 visitnum 8;
input subjid $ visitnum;
cards;
101-001-001 10001
101-001-001 30001
101-001-001 30015
101-001-001 30029
101-001-001 30043
101-001-001 70001
101-003-003 10001
101-003-003 20001
101-003-003 30001
101-003-003 30015
101-003-003 30029
101-003-003 30043
101-003-003 70001
101-003-004 10001
101-003-004 30001
101-003-004 30015
101-003-004 30029
101-003-004 30043
101-003-004 70001
;
run;
I need to generalize a program to expand the rows for each subject/visit based on number of timepoints for each visit (the timepoints table will be different for every study). I have been trying to create a macro to do this, but I can't seem to get it to work. I keep getting an extra row for VISITNUM=30001 and EGNUM=4. I'm not sure why. Can someone see why it runs one of the loops twice? This is the macro:
%macro expand();
proc sql;
select compress(put(count(*),best.)) into :ntmpts from timepoints;
select visitnum into :visit1-:visit&ntmpts. from timepoints;
select ptpt into :ptpt1-:ptpt&ntmpts. from timepoints;
select egnum into :egnum1-:egnum&ntmpts. from timepoints;
quit;
data subj_expand;
length ptpt $80;
set subj;
%do i=1 %to &ntmpts.;
if visitnum=&&visit&i. then do;
ptpt="&&ptpt&i.";
egnum=&&egnum&i.;
output;
end;
%end;
else output;
run;
%mend expand;
%expand;
This is an intense macro, but this is what I was able to do to get it to work. If anyone comes up with anything that is more simple, please feel free to pass it along!
data timepoints;
length visitnum 8 ptpt $12 egnum 8;
input visitnum ptpt $ egnum;
cards;
30001 PREDOSE 0.1
30001 PREDOSE 0.2
30001 PREDOSE 0.3
30001 4HR_POSTDOSE 4.0
30015 PREDOSE 0.0
30015 4HR_POSTDOSE 4.0
;
run;
data subj;
length subjid $11 visitnum 8;
input subjid $ visitnum;
cards;
101-001-001 10001
101-001-001 30001
101-001-001 30015
101-001-001 30029
101-001-001 30043
101-001-001 70001
101-003-003 10001
101-003-003 20001
101-003-003 30001
101-003-003 30015
101-003-003 30029
101-003-003 30043
101-003-003 70001
101-003-004 10001
101-003-004 30001
101-003-004 30015
101-003-004 30029
101-003-004 30043
101-003-004 70001
;
run;
%macro expand();
proc sql noprint;
select compress(put(count(distinct visitnum),best.)) into :nvisits from timepoints;
select distinct visitnum into :visit1-:visit&nvisits. from timepoints;
%do i=1 %to &nvisits.;
select compress(put(count(*),best.)) into :ntmpts_&&visit&i. from timepoints where visitnum=&&visit&i.;
select ptpt into :ptpt_&&visit&i.._1-:ptpt_&&visit&i.._&&&&ntmpts_&&visit&i. from timepoints where visitnum=&&visit&i.;
select egnum into :egnum_&&visit&i.._1-:egnum_&&visit&i.._&&&&ntmpts_&&visit&i.. from timepoints where visitnum=&&visit&i.;
%end;
quit;
data subj_expand;
length ptpt $80;
set subj;
if visitnum=&visit1. then do;
%do j=1 %to &&ntmpts_&visit1.;
ptpt="&&ptpt_&visit1._&j.";
egnum=&&egnum_&visit1._&j.;
output;
%end;
end;
%do i=2 %to &nvisits.;
else if visitnum=&&visit&i. then do;
%do j=1 %to &&&&ntmpts_&&visit&i.;
ptpt="&&&&ptpt_&&visit&i.._&j.";
egnum=&&&&egnum_&&visit&i.._&j.;
output;
%end;
end;
%end;
else output;
run;
%mend expand;
%expand;
What do you want as output?
I have attached an Excel file showing both what my output is and what I want it to look like. I have highlighted the extra rows in the table that is not what I want.
Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.
Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.
Thanks, but I don't necessarily need my table in a SAS dataset. I just needed a nice way to display it. Since SAS updated this community message board, we aren't able to insert nice tables anymore. If I just copy my table and paste it here, it typically is a big text mess. But it seemed to come out somewhat nice here. This is what I want my output table to look like:
ptpt | subjid | visitnum | egnum |
101-001-001 | 10001 | . | |
PREDOSE | 101-001-001 | 30001 | 0.1 |
PREDOSE | 101-001-001 | 30001 | 0.2 |
PREDOSE | 101-001-001 | 30001 | 0.3 |
4HR_POSTDOSE | 101-001-001 | 30001 | 4 |
PREDOSE | 101-001-001 | 30015 | 0 |
4HR_POSTDOSE | 101-001-001 | 30015 | 4 |
101-001-001 | 30029 | . | |
101-001-001 | 30043 | . | |
101-001-001 | 70001 | . | |
101-003-003 | 10001 | . | |
101-003-003 | 20001 | . | |
PREDOSE | 101-003-003 | 30001 | 0.1 |
PREDOSE | 101-003-003 | 30001 | 0.2 |
PREDOSE | 101-003-003 | 30001 | 0.3 |
4HR_POSTDOSE | 101-003-003 | 30001 | 4 |
PREDOSE | 101-003-003 | 30015 | 0 |
4HR_POSTDOSE | 101-003-003 | 30015 | 4 |
101-003-003 | 30029 | . | |
101-003-003 | 30043 | . | |
101-003-003 | 70001 | . | |
101-003-004 | 10001 | . | |
PREDOSE | 101-003-004 | 30001 | 0.1 |
PREDOSE | 101-003-004 | 30001 | 0.2 |
PREDOSE | 101-003-004 | 30001 | 0.3 |
4HR_POSTDOSE | 101-003-004 | 30001 | 4 |
PREDOSE | 101-003-004 | 30015 | 0 |
4HR_POSTDOSE | 101-003-004 | 30015 | 4 |
101-003-004 | 30029 | . | |
101-003-004 | 30043 | . | |
101-003-004 | 70001 | . |
@djbateman wrote:
I keep getting an extra row for VISITNUM=30001 and EGNUM=4. I'm not sure why. Can someone see why it runs one of the loops twice? This is the macro:
The Else output is only the else when &I = &ntmpts.
in effect you are doing:
if value =1 then do; <something>; end;
if value =2 then do; <something>; end;
if value =3 then do; <something>; end;
if value =4 then do; <something>; end;
else do; <something>; end;
I might suggest building a SELECT block to only have one "else"
select (visitnum);
%do I = 1 %to &ntmpts;
when (&&visit&i.) do;
ptpt="&&ptpt&i.";
egnum=&&egnum&i.;
output;
end;
%end;
otherwise output;
end;/* the select block*/
I gave the SELECT statement a try, and I ended up with fewer records than expected. The first timepoint for each visit was created (i.e, VISITNUM=30001 & EGNUM=0.1 and VISITNUM=30015 & EGNUM=0). None of the other timepoints were created.
This is an intense macro, but this is what I was able to do to get it to work. If anyone comes up with anything that is more simple, please feel free to pass it along!
data timepoints;
length visitnum 8 ptpt $12 egnum 8;
input visitnum ptpt $ egnum;
cards;
30001 PREDOSE 0.1
30001 PREDOSE 0.2
30001 PREDOSE 0.3
30001 4HR_POSTDOSE 4.0
30015 PREDOSE 0.0
30015 4HR_POSTDOSE 4.0
;
run;
data subj;
length subjid $11 visitnum 8;
input subjid $ visitnum;
cards;
101-001-001 10001
101-001-001 30001
101-001-001 30015
101-001-001 30029
101-001-001 30043
101-001-001 70001
101-003-003 10001
101-003-003 20001
101-003-003 30001
101-003-003 30015
101-003-003 30029
101-003-003 30043
101-003-003 70001
101-003-004 10001
101-003-004 30001
101-003-004 30015
101-003-004 30029
101-003-004 30043
101-003-004 70001
;
run;
%macro expand();
proc sql noprint;
select compress(put(count(distinct visitnum),best.)) into :nvisits from timepoints;
select distinct visitnum into :visit1-:visit&nvisits. from timepoints;
%do i=1 %to &nvisits.;
select compress(put(count(*),best.)) into :ntmpts_&&visit&i. from timepoints where visitnum=&&visit&i.;
select ptpt into :ptpt_&&visit&i.._1-:ptpt_&&visit&i.._&&&&ntmpts_&&visit&i. from timepoints where visitnum=&&visit&i.;
select egnum into :egnum_&&visit&i.._1-:egnum_&&visit&i.._&&&&ntmpts_&&visit&i.. from timepoints where visitnum=&&visit&i.;
%end;
quit;
data subj_expand;
length ptpt $80;
set subj;
if visitnum=&visit1. then do;
%do j=1 %to &&ntmpts_&visit1.;
ptpt="&&ptpt_&visit1._&j.";
egnum=&&egnum_&visit1._&j.;
output;
%end;
end;
%do i=2 %to &nvisits.;
else if visitnum=&&visit&i. then do;
%do j=1 %to &&&&ntmpts_&&visit&i.;
ptpt="&&&&ptpt_&&visit&i.._&j.";
egnum=&&&&egnum_&&visit&i.._&j.;
output;
%end;
end;
%end;
else output;
run;
%mend expand;
%expand;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.