BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
djbateman
Lapis Lazuli | Level 10

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;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
djbateman
Lapis Lazuli | Level 10

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;

View solution in original post

7 REPLIES 7
Reeza
Super User

What do you want as output?

djbateman
Lapis Lazuli | Level 10

 

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.

ballardw
Super User

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.

djbateman
Lapis Lazuli | Level 10

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:

 

ptptsubjidvisitnumegnum
 101-001-00110001.
PREDOSE101-001-001300010.1
PREDOSE101-001-001300010.2
PREDOSE101-001-001300010.3
4HR_POSTDOSE101-001-001300014
PREDOSE101-001-001300150
4HR_POSTDOSE101-001-001300154
 101-001-00130029.
 101-001-00130043.
 101-001-00170001.
 101-003-00310001.
 101-003-00320001.
PREDOSE101-003-003300010.1
PREDOSE101-003-003300010.2
PREDOSE101-003-003300010.3
4HR_POSTDOSE101-003-003300014
PREDOSE101-003-003300150
4HR_POSTDOSE101-003-003300154
 101-003-00330029.
 101-003-00330043.
 101-003-00370001.
 101-003-00410001.
PREDOSE101-003-004300010.1
PREDOSE101-003-004300010.2
PREDOSE101-003-004300010.3
4HR_POSTDOSE101-003-004300014
PREDOSE101-003-004300150
4HR_POSTDOSE101-003-004300154
 101-003-00430029.
 101-003-00430043.
 101-003-00470001.
ballardw
Super User

@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*/

djbateman
Lapis Lazuli | Level 10

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.

djbateman
Lapis Lazuli | Level 10

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;

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
  • 7 replies
  • 781 views
  • 0 likes
  • 3 in conversation