I have a dataset where the data looks like below
PRO-CTCAE 5 COMPOSITE 4 PRO0102C PRO-CTCAE Difficulty Swallowing (Composite) 60 PRO0102S
PRO-CTCAE 5 COMPOSITE 4 PRO0104C PRO-CTCAE Skin Cracking Corners Mouth (Composite) 61 PRO0104S
PRO-CTCAE 5 COMPOSITE 4 PRO0108C PRO-CTCAE Decreased Appetite at Worst (Composite) 62 PRO0108S PRO0108I
PRO-CTCAE 5 COMPOSITE 4 PRO0110C PRO-CTCAE Vomiting (Composite) 63 PRO0110F PRO0110S
PRO-CTCAE 5 COMPOSITE 4 PRO0111C PRO-CTCAE Heartburn (Composite) 64 PRO0111F PRO0111S
I have got one more dataset b as below
1280-0022-1840041007 Y Y Y Xe1000+Ev10+Ex25 10201000 1 53 PRO0117S PRO-CTCAE Pain in Abdomen (Severity) Pain in Abdomen (17) Severity 501 C05_V01 Cycle 5 0 NONE No worse than baseline 0 0 N 0 N N Y 5 PRO-CTCAE 1 ITEM 1 1
1280-0022-1840041007 Y Y Y Xe1000+Ev10+Ex25 10201000 1 53 PRO0117S PRO-CTCAE Pain in Abdomen (Severity) Pain in Abdomen (17) Severity 601 C06_V01 Cycle 6 0 NONE No worse than baseline 0 0 N 0 N N Y 5 PRO-CTCAE 1 ITEM 1 1
1280-0022-1840041007 Y Y Y Xe1000+Ev10+Ex25 10201000 1 53 PRO0117S PRO-CTCAE Pain in Abdomen (Severity) Pain in Abdomen (17) Severity 701 C07_V01 Cycle 7 0 NONE No worse than baseline 0 0 N 0 N N Y 5 PRO-CTCAE 1 ITEM 1 1
1280-0022-1840041007 Y Y Y Xe1000+Ev10+Ex25 10201000 1 53 PRO0117S PRO-CTCAE Pain in Abdomen (Severity) Pain in Abdomen (17) Severity 801 C08_V01 Cycle 8 1 MILD Mild/Moderate or Rarely/Occasionally or A little bit/Somewhat 0 1 Y 1 Y N Y 5 PRO-CTCAE 1 ITEM 2 1
I need to get a final dataset where all the records from dataset a must map with every individual subject and visit from b. Over here i dont have usubjid and visit variable in dataset a. I had tried using cross joins but it did not work, can someone please help me with this.
Once I clean up your data steps to use the same variable names and lengths I can replicate your output with a data step.
data want;
do outer=0 to nobs2;
do inner=1 to nobs1;
set have point=inner nobs=nobs1;
if outer then set parms point=outer nobs=nobs2;
output;
end;
end;
stop;
run;
It seems to match except for one extra right parenthesis typo in the expected data.
Number of Variables Compared with All Observations Equal: 20. Number of Variables Compared with Some Observations Unequal: 1. Total Number of Values which Compare Unequal: 1. Maximum Difference: 0. Variables with Unequal Values Variable Type Len Ndif MaxDif PARAM CHAR 52 1 Value Comparison Results for Variables __________________________________________________________ || Base Value Compare Value Obs || PARAM PARAM ________ || ___________________+ ___________________+ || 37 || PRO-CTCAE Decreased PRO-CTCAE Decreased __________________________________________________________
2607 proc compare data=want compare=expect; 2608 run; NOTE: There were 44 observations read from the data set WORK.WANT. NOTE: There were 44 observations read from the data set WORK.EXPECT. NOTE: PROCEDURE COMPARE used (Total process time): real time 0.00 seconds cpu time 0.01 seconds 2609 data _null_; 2610 set want(firstobs=37 obs=37 ) expect(firstobs=37 obs=37 in=inexpect); 2611 put inexpect= param= :$quote.; 2612 run; inexpect=0 PARAM="PRO-CTCAE Decreased Appetite at Worst (Composite)" inexpect=1 PARAM="PRO-CTCAE Decreased Appetite at Worst (Composite))" NOTE: There were 1 observations read from the data set WORK.WANT. NOTE: There were 1 observations read from the data set WORK.EXPECT.
It would certainly help to provide examples that can match and show what the result of matching them should be.
And the rules for how you expect them to match.
If you want anything resembling working code provide data in the form of data step code.
I think you missed the output still unfortunately?
You have no column names either?
please find the dataset that i had mentioned earlier
dataset a below
Data d1;
infile datalines dsd;
input parcat5 : $9.
parcat5n
parcat6 : $9.
parcat6n
paramcd : $8.
param : $49.
paramn;
datalines;
PRO-CTCAE,5,COMPOSITE,4,PRO0102C,"PRO-CTCAE Difficulty Swallowing (Composite)",60
PRO-CTCAE,5,COMPOSITE,4,PRO0104C,"PRO-CTCAE Skin Cracking Corners Mouth (Composite)",61
PRO-CTCAE,5,COMPOSITE,4,PRO0108C,"PRO-CTCAE Decreased Appetite at Worst (Composite)",62
;
run;
dataset b as below
Data d1;
infile datalines dsd;
input
USUBJID : $20.
TRT01PN
TRTFL : $1.
TRT01P : $13.
AVISITN
AVISIT : $7.
AVAL
AVALC : $10.
ABLFL : $1.
BASE
CHG
RANDFL : $1.
PEPFL : $1.
PARAMNN
PARAMCD : $8.
PARAM : $52.
PARCAT5N
PARCAT5 : $9.
PARCAT6N
PARCAT6 : $4.
NFBSIVFL : $1.;
datalines;
1280-0022-1036002001,99201000,Y,Plc+Ev10+Ex25,101,C01_V01,1,NONE,Y,1,.,Y,Y,44,PRO0104S,"PRO-CTCAE Skin Cracking Corners Mouth (Severity)",5,PRO-CTCAE,1,ITEM,Y
1280-0022-1036002001,99201000,Y,Plc+Ev10+Ex25,101,C01_V01,1,NONE,Y,1,.,Y,Y,45,PRO0108S,"PRO-CTCAE Decreased Appetite at Worst (Severity)",5,PRO-CTCAE,1,ITEM,Y
1280-0022-1036002001,99201000,Y,Plc+Ev10+Ex25,102,C01_V01,4,NOT AT ALL,Y,1,.,Y,Y,46,PRO0108I,"PRO-CTCAE Decreased Appetite at Worst (Interference)",5,PRO-CTCAE,1,ITEM,Y
1280-0022-1036002001,99201000,Y,Plc+Ev10+Ex25,102,C01_V01,3,NEVER,Y,1,.,Y,Y,47,PRO0110F,"PRO-CTCAE Vomiting (Frequency)",5,PRO-CTCAE,1,ITEM,Y
1280-0022-1036002001,99201000,Y,Plc+Ev10+Ex25,103,C01_V01,1,NONE,Y,1,.,Y,Y,48,PRO0110S,"PRO-CTCAE Vomiting (Severity)",5,PRO-CTCAE,1,ITEM,Y
1280-0022-1036002001,99201000,Y,Plc+Ev10+Ex25,103,C01_V01,3,NEVER,Y,1,.,Y,Y,49,PRO0111F,"PRO-CTCAE Heartburn (Frequency)",5,PRO-CTCAE,1,ITEM,Y
1280-0022-1036002001,99201000,Y,Plc+Ev10+Ex25,104,C01_V01,1,NONE,Y,1,.,Y,Y,50,PRO0111S,"PRO-CTCAE Heartburn (Severity)",5,PRO-CTCAE,1,ITEM,Y
1280-0022-1036002001,99201000,Y,Plc+Ev10+Ex25,104,C01_V01,3,NEVER,Y,1,.,Y,Y,51,PRO0116F,"PRO-CTCAE Diarrhoea (Frequency)",5,PRO-CTCAE,1,ITEM,Y
1280-0022-1036002001,99201000,Y,Plc+Ev10+Ex25,104,C01_V01,3,NEVER,Y,1,.,Y,Y,52,PRO0117F,"PRO-CTCAE Pain in Abdomen (Frequency)",5,PRO-CTCAE,1,ITEM,Y
1280-0022-1036002001,99201000,Y,Plc+Ev10+Ex25,105,C01_V01,1,NONE,Y,1,.,Y,Y,53,PRO0117S,"PRO-CTCAE Pain in Abdomen (Severity)",5,PRO-CTCAE,1,ITEM,Y
1280-0022-1036002001,99201000,Y,Plc+Ev10+Ex25,106,C01_V01,4,NOT AT ALL,Y,1,.,Y,Y,54,PRO0117I,"PRO-CTCAE Pain in Abdomen (Interference)",5,PRO-CTCAE,1,ITEM,Y
;
run;
@Ravindra_ wrote:
Hello Reeza, can i know if there is any solution that you found for this? thank you
You need to explain what you are trying to DO.
Looking at your input and output you are going from 11 observations to 44. The first 11 seem to just be the input replicated unchanged. The other 33 observations seem to be the data repeated once for each observation in the parameter dataset. But other things including the order of the observations have changed.
What are the KEY variables for the three datasets?
What is the reason for replicating the input 4 times into the output?
@Ravindra_ wrote:
The first dataset is the composite dataset we need to map that with the master dataset b, here the dataset a need to be mapped with each subject and avisit from dataset b , this is the specification requirement as we will need to have original records from dataset b and also each record from a mapped to all subjects and avisit from b and then we will be merging this final dataset with a scoring dataset to do further analysis.
Using what variables to link/replicate?
Once I clean up your data steps to use the same variable names and lengths I can replicate your output with a data step.
data want;
do outer=0 to nobs2;
do inner=1 to nobs1;
set have point=inner nobs=nobs1;
if outer then set parms point=outer nobs=nobs2;
output;
end;
end;
stop;
run;
It seems to match except for one extra right parenthesis typo in the expected data.
Number of Variables Compared with All Observations Equal: 20. Number of Variables Compared with Some Observations Unequal: 1. Total Number of Values which Compare Unequal: 1. Maximum Difference: 0. Variables with Unequal Values Variable Type Len Ndif MaxDif PARAM CHAR 52 1 Value Comparison Results for Variables __________________________________________________________ || Base Value Compare Value Obs || PARAM PARAM ________ || ___________________+ ___________________+ || 37 || PRO-CTCAE Decreased PRO-CTCAE Decreased __________________________________________________________
2607 proc compare data=want compare=expect; 2608 run; NOTE: There were 44 observations read from the data set WORK.WANT. NOTE: There were 44 observations read from the data set WORK.EXPECT. NOTE: PROCEDURE COMPARE used (Total process time): real time 0.00 seconds cpu time 0.01 seconds 2609 data _null_; 2610 set want(firstobs=37 obs=37 ) expect(firstobs=37 obs=37 in=inexpect); 2611 put inexpect= param= :$quote.; 2612 run; inexpect=0 PARAM="PRO-CTCAE Decreased Appetite at Worst (Composite)" inexpect=1 PARAM="PRO-CTCAE Decreased Appetite at Worst (Composite))" NOTE: There were 1 observations read from the data set WORK.WANT. NOTE: There were 1 observations read from the data set WORK.EXPECT.
Thanks a lot Tom, you are the saviour, it worked for me, but can you please explain the code a bit as the point function is new to me. Can you please debug the code and explain me in detail, may be i can apply the same in other projects as well by learning a new thing. Thank you
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.