need to replace numbers with template variables

[vraj1]

I have a dataset which attached has description and need to replace it with template which i created.

I tried to use set but unsuccessfull. Attached both the datasets.

I am using below step to merge the numbers in stermfnl with termcat variable.

Can anyone help me on this

[Kurt_Bremser]

I can recreate your datasets very fine, but where is the merge code you tried?

[vraj1]

i am using the below code-

data final;
merge stermfnl(in=a) termreas;
by termresn aperiod aperiodc;
if a;
run;

[Kurt_Bremser]

When you do a data step merge, you need to take care of variables that have the same name, but are not included in the by statement.

Either drop those you do not need, or rename them in a dataset option, so you can use them with the new name in the data step.

It would be helpful if you included how, say, the first 10 rows should look like after the merge.

[vraj1]

in the current output after merging both the datasets i get 2 variables one with number which has values and the other with 0.

attached example.

proc sort data = stermfnl;
by termresn aperiod aperiodc;
run;
proc sort data = termreas;
by termresn aperiod aperiodc;
run;
data final;
merge stermfnl(in=a) termreas(drop=count disc_pct);
by termresn ;
if a;
run;

data inc;
set stermfnl;
where termresn in (-6,-3);
run;

data final1;
set final inc;
run;

[Kurt_Bremser]

When I run your code:

proc sort data = stermfnl;
by termresn aperiod aperiodc;
run;

proc sort data = termreas;
by termresn aperiod aperiodc;
run;

data final;
merge
  stermfnl (in=a)
  termreas (drop=count disc_pct)
;
by termresn aperiod aperiodc;
if a;
run;

data inc;
set stermfnl;
where termresn in (-6,-3);
run;

data final1;
set
  final
  inc
;
run;

options pagesize=31767;
proc print data=final1 noobs;
run;

I get this result:

    dose_tot      termcat                                                      APERIOD APERIODC count termresn totcount disc_pct

PHASE A?(N = 466) ENROLLED                                                         1   PHASE A   466      -6      466     100.0 
PHASE B?(N = 632) ENROLLED                                                         2   PHASE B   632      -6      632     100.0 
PHASE C?(N = 425) ENROLLED                                                         3   PHASE C   425      -6      425     100.0 
PHASE D?(N = 266) ENROLLED                                                         4   PHASE D   266      -6      266     100.0 
TOTAL?(N = 731)   ENROLLED                                                        99   TOTAL     731      -6      731     100.0 
PHASE A?(N = 466) DISCONTINUED                                                     1   PHASE A    99      -3      466      21.2 
PHASE B?(N = 632) DISCONTINUED                                                     2   PHASE B   207      -3      632      32.8 
PHASE C?(N = 425) DISCONTINUED                                                     3   PHASE C   159      -3      425      37.4 
PHASE D?(N = 266) DISCONTINUED                                                     4   PHASE D   164      -3      266      61.7 
TOTAL?(N = 731)   DISCONTINUED                                                    99   TOTAL     629      -3      731      86.0 
PHASE A?(N = 466) LOST TO FOLLOW-UP                                                1   PHASE A    16       2      466       3.4 
PHASE B?(N = 632) LOST TO FOLLOW-UP                                                2   PHASE B    44       2      632       7.0 
PHASE C?(N = 425) LOST TO FOLLOW-UP                                                3   PHASE C    21       2      425       4.9 
PHASE D?(N = 266) LOST TO FOLLOW-UP                                                4   PHASE D    13       2      266       4.9 
TOTAL?(N = 731)   LOST TO FOLLOW-UP                                               99   TOTAL      94       2      731      12.9 
PHASE A?(N = 466) ADVERSE EVENTS                                                   1   PHASE A    33       3      466       7.1 
PHASE B?(N = 632) ADVERSE EVENTS                                                   2   PHASE B    63       3      632      10.0 
PHASE C?(N = 425) ADVERSE EVENTS                                                   3   PHASE C    37       3      425       8.7 
TOTAL?(N = 731)   ADVERSE EVENTS                                                  99   TOTAL     133       3      731      18.2 
PHASE C?(N = 425) SPONSOR DISCONTINUED STUDY                                       3   PHASE C     1       4      425       0.2 
PHASE D?(N = 266) SPONSOR DISCONTINUED STUDY                                       4   PHASE D     4       4      266       1.5 
TOTAL?(N = 731)   SPONSOR DISCONTINUED STUDY                                      99   TOTAL       5       4      731       0.7 
PHASE A?(N = 466) SUBJECT MET WITHDRAWAL CRITERIA                                  1   PHASE A    10       5      466       2.1 
PHASE B?(N = 632) SUBJECT MET WITHDRAWAL CRITERIA                                  2   PHASE B    41       5      632       6.5 
PHASE C?(N = 425) SUBJECT MET WITHDRAWAL CRITERIA                                  3   PHASE C    26       5      425       6.1 
PHASE D?(N = 266) SUBJECT MET WITHDRAWAL CRITERIA                                  4   PHASE D    11       5      266       4.1 
TOTAL?(N = 731)   SUBJECT MET WITHDRAWAL CRITERIA                                 99   TOTAL      88       5      731      12.0 
PHASE A?(N = 466) SUBJECT WAS WITHDRAWN FROM PARTICIPATION BY THE INVESTIGATOR     1   PHASE A     3       6      466       0.6 
PHASE B?(N = 632) SUBJECT WAS WITHDRAWN FROM PARTICIPATION BY THE INVESTIGATOR     2   PHASE B     2       6      632       0.3 
PHASE C?(N = 425) SUBJECT WAS WITHDRAWN FROM PARTICIPATION BY THE INVESTIGATOR     3   PHASE C     6       6      425       1.4 
TOTAL?(N = 731)   SUBJECT WAS WITHDRAWN FROM PARTICIPATION BY THE INVESTIGATOR    99   TOTAL      11       6      731       1.5 
PHASE A?(N = 466) SUBJECT WITHDREW CONSENT TO PARTICIPATE                          1   PHASE A    32       7      466       6.9 
PHASE B?(N = 632) SUBJECT WITHDREW CONSENT TO PARTICIPATE                          2   PHASE B    45       7      632       7.1 
PHASE C?(N = 425) SUBJECT WITHDREW CONSENT TO PARTICIPATE                          3   PHASE C    56       7      425      13.2 
PHASE D?(N = 266) SUBJECT WITHDREW CONSENT TO PARTICIPATE                          4   PHASE D    23       7      266       8.6 
TOTAL?(N = 731)   SUBJECT WITHDREW CONSENT TO PARTICIPATE                         99   TOTAL     156       7      731      21.3 
PHASE A?(N = 466) PROTOCOL DEVIATION                                               1   PHASE A     3       8      466       0.6 
PHASE C?(N = 425) PROTOCOL DEVIATION                                               3   PHASE C     5       8      425       1.2 
PHASE D?(N = 266) PROTOCOL DEVIATION                                               4   PHASE D     2       8      266       0.8 
TOTAL?(N = 731)   PROTOCOL DEVIATION                                              99   TOTAL      10       8      731       1.4 
PHASE A?(N = 466) LACK OF EFFICACY                                                 1   PHASE A     2      21      466       0.4 
PHASE B?(N = 632) LACK OF EFFICACY                                                 2   PHASE B    12      21      632       1.9 
PHASE C?(N = 425) LACK OF EFFICACY                                                 3   PHASE C     7      21      425       1.6 
TOTAL?(N = 731)   LACK OF EFFICACY                                                99   TOTAL      21      21      731       2.9 
PHASE D?(N = 266) ADVERSE EVENT WITHOUT RECURRENCE OF ANY MOOD EPISODE             4   PHASE D     8      32      266       3.0 
TOTAL?(N = 731)   ADVERSE EVENT WITHOUT RECURRENCE OF ANY MOOD EPISODE            99   TOTAL       8      32      731       1.1 
PHASE D?(N = 266) RECURRENCE OF ANY MOOD EPISODE WITH ADVERSE EVENT                4   PHASE D    49      33      266      18.4 
TOTAL?(N = 731)   RECURRENCE OF ANY MOOD EPISODE WITH ADVERSE EVENT               99   TOTAL      49      33      731       6.7 
PHASE D?(N = 266) RECURRENCE OF ANY MOOD EPISODE WITHOUT ADVERSE EVENT             4   PHASE D    54      34      266      20.3 
TOTAL?(N = 731)   RECURRENCE OF ANY MOOD EPISODE WITHOUT ADVERSE EVENT            99   TOTAL      54      34      731       7.4 
PHASE A?(N = 466) ENROLLED                                                         1   PHASE A   466      -6      466     100.0 
PHASE B?(N = 632) ENROLLED                                                         2   PHASE B   632      -6      632     100.0 
PHASE C?(N = 425) ENROLLED                                                         3   PHASE C   425      -6      425     100.0 
PHASE D?(N = 266) ENROLLED                                                         4   PHASE D   266      -6      266     100.0 
TOTAL?(N = 731)   ENROLLED                                                        99   TOTAL     731      -6      731     100.0 
PHASE A?(N = 466) DISCONTINUED                                                     1   PHASE A    99      -3      466      21.2 
PHASE B?(N = 632) DISCONTINUED                                                     2   PHASE B   207      -3      632      32.8 
PHASE C?(N = 425) DISCONTINUED                                                     3   PHASE C   159      -3      425      37.4 
PHASE D?(N = 266) DISCONTINUED                                                     4   PHASE D   164      -3      266      61.7 
TOTAL?(N = 731)   DISCONTINUED                                                    99   TOTAL     629      -3      731      86.0 

No zeroes anywhere.