BookmarkSubscribeRSS Feed
vraj1
Quartz | Level 8

I have a question. in the attached data i need to make chg and pchg blank if aval is missing for visitnum 1. i.e if it is same studyid and same param then if first visit is missing i need to have chg and pchg as blank for the rest of the records. any help

 

proc sort data=inc;  by studyid usubjid aval visitnum; run;
data kd;
   set inc;
   by studyid usubjid aval visitnum;
   retain chg pchg;
     
   if first.aval and aval=.  then 
      CHG=.;
	  PCHG=.;
	if last.aval then output;
run;

 

10 REPLIES 10
PaigeMiller
Diamond | Level 26

What is the problem when you run this code? Don't make us do work to find the problem, when you already know what the problem is.

--
Paige Miller
Kurt_Bremser
Super User

Please supply example data that is actually used in your code, or adapt the code to the example data posted.

They don't match in dataset name and structure.


@vraj1 wrote:

I have a question. in the attached data i need to make chg and pchg blank if aval is missing for visitnum 1. i.e if it is same studyid and same param then if first visit is missing i need to have chg and pchg as blank for the rest of the records. any help

 

proc sort data=inc;  by studyid usubjid aval visitnum; run;
data kd;
   set inc;
   by studyid usubjid aval visitnum;
   retain chg pchg;
     
   if first.aval and aval=.  then 
      CHG=.;
	  PCHG=.;
	if last.aval then output;
run;

 

data WORK.MYDATA;
  infile datalines dsd truncover;
  input F1:$6. F2:32. F3:$6. F4:$5. F5:$24. F6:$5. F7:32. F8:DATE9.;
datalines4;
usubj,,day,value,visit,study,visitnum,
HED101,0,Day 0,20.2,Visit 1 (Screening),NN11,1,30NOV2017
HED101,28,Day 28,20.2,Visit 6 (End of Week 4),NN11,34,12JAN2018
HED101,,,20.2,,NN11,34,
HED101,56,Day 56,20.2,Visit 10 (End of Week 8),NN11,61,07FEB2018
HED101,84,Day 84,20.2,Visit 1 (timeH),NN11,1,06MAR2018
HED101,84,Day 84,20.2,Visit 12 (measure),NN11,76,07MAR2018
HED101,,,20.2,,NN11,76,
HED101,0,Day 0,20.2,Visit 6 (End of Week 4),NN11,34,12JAN2018
HED101,28,Day 28,20.2,Visit 10 (End of Week 8),NN11,61,07FEB2018
HED101,56,Day 56,20.2,Visit 1 (timeH),NN11,1,06MAR2018
HED101,56,Day 56,20.2,Visit 12 (measure),NN11,76,07MAR2018
HED101,,,20.2,,NN11,76,
HED101,0,Day 0,20,Visit 1 (timeH),NN12,,
;;;;


 

vraj1
Quartz | Level 8
data WORK.MYDATA;
  infile datalines dsd truncover;
  input usubjid:$16. studyid:$3. paramcd:$4. aval:$3. ablf:$1. base:$3. chg:$3. pchg:$2. visit:$9. visitnum:32.;
datalines4;
AA1-US1103-S3317,AA1,URIN,.,,.,.,.,Visit 1,1
AA1-US1103-S3317,AA1,URIN,3.6,,.,.,.,,
AA1-US1103-S3317,AA1,URIN,3.6,,.,.,.,Visit 6,6
AA1-US1103-S3317,AA1,URIN,3.6,Y,3.6,0,0,Visit 6,6
AA1-US1103-S3317,AA1,URIN,4.2,,3.6,.,.,Visit 10,10
AA1-US1103-S3317,AA1,URIN,4.2,,3.6,0.6,17,Visit 10,10
AA1-US1103-S3317,AA1,URIN,5.6,,.,.,.,,
AA1-US1103-S3317,AA1,URIN,5.6,,3.6,2,56,,
AA1-US1103-S3317,AA1,URIN,5.6,,3.6,.,.,Visit 12,12
AA1-US1103-S3317,AA1,URIN,5.6,,3.6,2,56,Visit 12,12
;;;;

proc sort data=WORK.MYDATA;  by studyid usubjid aval visitnum; run;
data kd;
   set WORK.MYDATA;
   by studyid usubjid aval visitnum;
   retain chg pchg;
     
   if first.aval and aval=.  then 
      CHG=.;
	  PCHG=.;
	if last.aval then output;
run;

this is my data and code and i have more studyid and usubjid and param and want to make sure if the visitnumis 1 and aval is missing then chg and pchg should be blank.

SuryaKiran
Meteorite | Level 14

You have CHG and PCHG as characters and your giving "." (This will not be missing for character). Give them blank value instead.

Thanks,
Suryakiran
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Errm, why is aval, base chg pchg all character?  Why is there a couple of rows with missing visit information?

The data you present looks correct in terms of change from baseline, ablf is marked Y and base value is present from there on, as is change, which is as you would expect.  Its not relevant if visit 1 has no data, as there is a baseline.  If you remove this then you need to blank all the data items, ablf, base, chg, pchg.  E.g.

data want (drop=blank);
  set have;
  by usubjid;
  if first.usubjid and aval="" then blank="Y";
  else if blank="Y" then do;
    ablf="";
    base="";
    chg="":
    pchg="";
  end;
run;
Astounding
PROC Star

There are many questions ... are all these variables really character?  Can VISIT really have a missing value on some observations?

 

I'm going to use the data the way you presented it.  You may need to change some statements if your data really is different (such as if CHG is actually a numeric variable):

 

proc sort data=have;

by usubjid studyid paramcd;

run;

 

data want;

do until (last.paramcd);

   set have;

   by usubjid studyid paramcd;

   if visit=1 and aval=. then remove_flag=1;

end;

do until (last.paramcd);

   set have;

   by usubjid studyid paramcd;

   if remove_flag=1 then do;

      call missing(chg);

      call missing(pchg);

   end;

   output;

end;

drop remove_flag;

run;

 

******************* EDITED:  allowing that some variables might be numeric instead of character.

 

AVAL should be numeric.  The code would work regardless of whether CHG and PCHG are character or numeric.

vraj1
Quartz | Level 8

Thew values are numeric and as the dataset is in excel when i imported it became character, but somehow it is not working.

attaching new data. ignore the values as it is test data but if visitnum is 1 with same studyid,usubjid,paramcd  and aval is missing then the rest of the records for same studyid,usubjid,paramcd  chg and pchg should be missing.

data WORK.MYDATA;
  infile datalines dsd truncover;
  input usubjid:$16. studyid:$3. paramcd:$5. aval:3. ablf:$1. base:3. chg:3. pchg:2. visit:$9. visitnum:32.;
datalines4;
AA1-US1103-S3317,AA1,URIN,.,,,.,.,Visit 1,1,,
AA1-US1103-S3317,AA1,URIN,3.6,,.,.,.,,,,
AA1-US1103-S3317,AA1,URIN,3.6,,.,.,.,Visit 6,6,,
AA1-US1103-S3317,AA1,URIN,3.6,Y,3.6,0,0,Visit 6,6,,
AA1-US1103-S3317,AA1,URIN,4.2,,3.6,.,.,Visit 10,10,,
AA1-US1103-S3317,AA1,URIN,4.2,,3.6,0.6,17,Visit 10,10,,
AA1-US1103-S3317,AA1,URIN,5.6,,3.6,2,56,,,,
AA1-US1103-S3317,AA1,URIN,5.6,,3.6,.,.,Visit 12,12,,
AA1-US1103-S3317,AA1,URIN,5.6,,3.6,2,56,Visit 12,12,,
AA1-US1103-S2317,AA1,PLASM,39,Y,42,-3,-7,Visit 1,1,,
AA1-US1103-S2317,AA1,PLASM,42,,42,0,0,,,,
AA1-US1103-S2317,AA1,PLASM,42,,39,3,8,Visit 5,5,,
AA1-US1103-S2317,AA1,PLASM,42,,42,.,.,Visit 6,6,,
AA1-US1103-S2317,AA1,PLASM,39,,42,-3,-7,Visit 10,10,,
AA1-US1103-S2317,AA1,PLASM,39,,39,0,0,Visit 10,10,,
AA1-US1103-S2317,AA1,PLASM,40,,42,-2,-5,,,,
AA1-US1103-S2317,AA1,URIN,40,,40,0,3,Visit 1,1,,
AA1-US1103-S2317,AA1,URIN,42,,40,0,0,Visit 12,12,,
AA1-US1103-S2317,AA1,URIN,42,,40,-2,8,Visit 12,12,,
;;;;

Sorry for the trouble but somehow it is mystery why it is not working.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

I think your going at it the wrong way round.  Some steps:

Step one, extract baseline record for each by group into a separate dataset.

2 - merge this baseline record back to main data on by group

3 - if value and baseline value both non-missing calculate chg as value - baseline value

 

vraj1
Quartz | Level 8

I only have this dataset which has values for chg already and now i want to remove it. I do not have previous code for it

SuryaKiran
Meteorite | Level 14

Please provide the appropriate data your have. Seems like the code you shared is generated by import wizard, in datalines you have your variables names. If your using the import wizard to created the sample data then check the option "First row contains variable name" 

 

Check this how to create a sample data https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

 

Thanks,
Suryakiran

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
  • 10 replies
  • 2028 views
  • 0 likes
  • 6 in conversation