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;
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.
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,, ;;;;
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.
You have CHG and PCHG as characters and your giving "." (This will not be missing for character). Give them blank value instead.
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;
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.
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.
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
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
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...
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.