How to map few columns from a dataset to the whole dataset of the other with few similar variables

[Ravindra_]

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.

[ballardw]

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.

[Ravindra_]

I had added the columns and dataset as well now as an example, can i get the help please

[Reeza]

I think you missed the output still unfortunately?

[Ravindra_]

Hi ballardw, can i know if you have got any solution for this?

[Reeza]

You have no column names either?

[Ravindra_]

I had posted the dataset below, can you please help me

[Ravindra_]

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;

[Reeza]

And expected output?

[Ravindra_]

Hello Reeza, can i know if there is any solution that you found for this? thank you

[Tom]

---

@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_]

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.

[Reeza]

---

@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?

[Tom · Accepted solution]

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.

[Ravindra_]

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