topic Re: want to make few variables blank if the condition satisfies in SAS Programming

want to make few variables blank if the condition satisfies

vraj1 — Thu, 19 Apr 2018 12:24:52 GMT

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;

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

PaigeMiller — Thu, 19 Apr 2018 12:30:05 GMT

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.

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

Kurt_Bremser — Thu, 19 Apr 2018 12:29:51 GMT

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:

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

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

RW9 — Thu, 19 Apr 2018 12:35:20 GMT

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

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

vraj1 — Thu, 19 Apr 2018 12:37:59 GMT

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

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

SuryaKiran — Thu, 19 Apr 2018 12:58:41 GMT

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-data-AKA-generate/ta-p/258712

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

vraj1 — Thu, 19 Apr 2018 13:04:28 GMT

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.

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

SuryaKiran — Thu, 19 Apr 2018 13:11:17 GMT

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

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

RW9 — Thu, 19 Apr 2018 13:11:28 GMT

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;

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

Astounding — Thu, 19 Apr 2018 15:25:57 GMT

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.

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

vraj1 — Thu, 19 Apr 2018 15:02:46 GMT

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.