DATA Step, Macro, Functions and more

want to make few variables blank if the condition satisfies

Reply
Regular Contributor
Posts: 214

want to make few variables blank if the condition satisfies

[ Edited ]

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;

 

Attachment
Respected Advisor
Posts: 3,066

Re: want to make few variables blank if the condition satisfies

[ Edited ]

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
Super User
Posts: 10,280

Re: want to make few variables blank if the condition satisfies

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,,
;;;;


 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Regular Contributor
Posts: 214

Re: want to make few variables blank if the condition satisfies

Posted in reply to KurtBremser
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.

Valued Guide
Posts: 597

Re: want to make few variables blank if the condition satisfies

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

Thanks,
Suryakiran
Super User
Super User
Posts: 9,599

Re: want to make few variables blank if the condition satisfies

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;
Super User
Posts: 6,785

Re: want to make few variables blank if the condition satisfies

[ Edited ]

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.

Regular Contributor
Posts: 214

Re: want to make few variables blank if the condition satisfies

[ Edited ]
Posted in reply to Astounding

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.

Super User
Super User
Posts: 9,599

Re: want to make few variables blank if the condition satisfies

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

 

Regular Contributor
Posts: 214

Re: want to make few variables blank if the condition satisfies

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

Valued Guide
Posts: 597

Re: want to make few variables blank if the condition satisfies

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
Ask a Question
Discussion stats
  • 10 replies
  • 140 views
  • 0 likes
  • 6 in conversation