Are those all the variables in your data set? It's hard for us to tell from a screenshot. Maybe run a PROC CONTENTS just to show the variables so we can be certain it's not actually in your data set. ... View more

I've never used the PSMATCH procedure, but @Tom seems to be right. I looked up the user guide and it seemed to go along with that idea.   https://support.sas.com/documentation/onlinedoc/stat/142/psmatch.pdf   Page 7687   and the MATCHID=_MatchID option creates a variable named _MatchID that identifies the matched sets of observations. ... View more

If you're comparing two different data sets, you have to make sure they share something in common like an ID.   data first; set sashelp.class; run; data second; set sashelp.cars; run; proc compare data = first compare = second; run; Look into PROC COMPARE -- it's a SAS procedure that does exactly what you want without having to code it. Note that I just did a raw compare of both files -- it found one variable in common (weight), but it has no substantive significance (comparing car weights to school childrens' weights).   There are many statements in PROC COMPARE that allow you to select variables, identify observations, etc. You can create datasets from the PROC COMPARE procedure too.   There are many ways to do this, but just go with what SAS has already done for you.     ... View more

DATA step merges cannot do many-to-many joins.   Note: The MERGE statement does not produce a Cartesian product on a many-to-many match-merge. Instead, it performs a one-to-one merge while there are observations in the BY group in at least one data set. When all observations in the BY group have been read from one data set and there are still more observations in another data set, SAS performs a one-to-many merge until all BY group observations have been read.   https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lestmtsref/n1i8w2bwu1fn5kn1gpxj18xttbb0.htm   What you're showing is an inner join.   You also need to be careful if you share any variable names in the data sets in a MERGE statement. SAS takes the right-most value. ... View more

proc transpose data = have (keep = id revenue:) out = want_rev (drop = _name_) prefix = revenue; by id; var revenue; run; proc transpose data = have (keep = id profit:) out = want_prof (drop = _name_) prefix = profit; by id; var profit; run; data want; merge want_rev want_prof; by id; run; ... View more

Okay, a couple things to tackle here:   1. We need a reproducible example. I'm not sure what the confidentiality is regarding your data, but this is a great macro to get it in a workable form for us: https://blogs.sas.com/content/sastraining/2016/03/11/jedi-sas-tricks-data-to-data-step-macro/  2. To me, it may be best to keep your data in long form. The amount of merges you're doing seems like unnecessary work.   I may not understand your issue, so (2) may be out of line, but (2) depends on (1). ... View more

Probably more efficient ways of doing this -- kind of a mess with the `CATX` function.   data have; infile datalines delimiter = "," dsd; input Year PCP_Visit_past_year :$10. n Row_Percent Lower_CL Upper_CL; datalines; 2016,Yes,628,84.6800,81.2606,88.0995 2016,No,110,14.5566,11.1867,17.9265 2016,Never vist,7,0.7634,0.0458,1.4809 2017,Yes,805,87.8194,84.9743,90.6645 2017,No,108,11.8672,9.0415,14.6928 2017,Never vist,4,0.3135,0.0000,0.6998 2018,Yes,583,86.1388,82.9866,89.2909 2018,No,82,13.2549,10.1490,16.3609 2018,Never vist,4,0.6063,0.0000,1.2480 2019,Yes,537,84.6572,80.5744,88.7401 2019,No,100,14.9445,10.8877,19.0013 2019,Never vist,6,0.3983,0.0000,0.9672 2020,Yes,492,84.5874,80.6830,88.4919 2020,No,94,14.3637,10.5594,18.1679 2020,Never vist,8,1.0489,0.0061,2.0916 ; run; proc sql; create table have_1 as select year, pcp_visit_past_year, catx(" ", cat(row_percent, "%"), cat("(", lower_cl), cat(" - ", upper_cl, ")")) as cl from have order by pcp_visit_past_year, year; quit; proc transpose data = have_1 out = want (drop = _name_); id year; by pcp_visit_past_year; var cl; run; You can't have variable names with only numbers. `PROC TRANSPOSE` inserts an `_` to accommodate this issue.   Obs PCP_Visit_past_year _2016 _2017 _2018 _2019 _2020 1 Never vist 0.7634% (0.0458 - 1.4809) 0.3135% (0 - 0.6998) 0.6063% (0 - 1.248) 0.3983% (0 - 0.9672) 1.0489% (0.0061 - 2.0916) 2 No 14.5566% (11.1867 - 17.9265) 11.8672% (9.0415 - 14.6928) 13.2549% (10.149 - 16.3609) 14.9445% (10.8877 - 19.0013) 14.3637% (10.5594 - 18.1679) 3 Yes 84.68% (81.2606 - 88.0995) 87.8194% (84.9743 - 90.6645) 86.1388% (82.9866 - 89.2909) 84.6572% (80.5744 - 88.7401) 84.5874% (80.683 - 88.4919) ... View more

I think this takes care of it. The `PROC FREQ` with the `SPARSE` option expands the table to include the missing `avisitn` records.   The rest just is some PDV manipulation.   proc freq data = visit noprint; tables id * avisitn / out = visit_1 (drop = percent count) sparse; run; data visit_2; merge visit visit_1; by id avisitn; retain result2; if first.id then result2 = .; if result ~= . then result2 = result; run; ID avisitn result result2 A 0 4.1 4.1 A 1 3.9 3.9 A 2 4.3 4.3 A 4 5.0 5.0 A 8 4.5 4.5 A 12 5.1 5.1 A 16 5.3 5.3 A 18 4.9 4.9 A 20 4.7 4.7 B 0 4.2 4.2 B 1 3.7 3.7 B 2 4.4 4.4 B 4 5.2 5.2 B 8 . 5.2 B 12 4.5 4.5 B 16 5.2 5.2 B 18 4.8 4.8 B 20 4.9 4.9 C 0 4.6 4.6 C 1 4.9 4.9 C 2 5.3 5.3 C 4 . 5.3 C 8 5.5 5.5 C 12 . 5.5 C 16 5.6 5.6 C 18 5.9 5.9 C 20 5.7 5.7 ... View more

This does it, but it depends on exactly what you want. If you want duplicates removed, you can omit the `ALL` from the `UNION` operator.   proc sql; create table want2 as select * from have1 union corr all select * from have2; quit; var1 var2 11 21 12 22 13 23 14 24 15 25 16 26 I think that's the difference between the two, but someone can correct me if I'm mistaken. ... View more