Joining Tables

[Ryan199900]

I’m fairly new to SAS but I have a dataset that looks like this:

Name 2020 2021 2022
A 3 74 72
A 45 21 5
A 4 9 10

There is much more data (names and years I would like for the column variables to repeat and all fall under one line.

So:

Name 2020 2021 2022 2020 2021
A. 3 74. 72. 45. 21

Continued

[PaigeMiller]

You can't have a SAS data set that looks like the one you say you have, because column names cannot begin with a digit. For your output data set, you can't have the same name appear twice (or more than twice) in your list of variables.

So, you have presented a problem that can't be accomplished in SAS ... perhaps you could re-state the problem such that it is possible to do in SAS.

Lastly, since you are new at SAS, re-arranging the data like this, with calendar information in the variable names, is almost always a bad idea. What are you going to do with this data once it is re-arranged? What analysis or report or table will you create?

[Kurt_Bremser]

Your expected result looks more like a report than a dataset. What should differentiate the multiple values for a given name and year?

[Tom]

Hard to tell what you are trying to do.  I sounds like you want to produce a REPORT that looks like this:

Which is easy if you organize your data in the right way with a variables for NAME, YEAR, ROW (or repetition) and VALUE.

proc report data=have;
  column name value,row,year;
  define name / group;
  define row / across ' ';
  define year / across ' ';
  define value / sum ' ';
run;

You could build such a dataset from your original listing easily.

data have;
  input name $ @;
  row+1;
  do year=2020, 2021, 2022;
    input value @;
    output;
  end;
cards;
A 3 74 72
A 45 21 5
A 4 9 10
;

Example data

Obs    name    row    year    value

 1      A       1     2020       3
 2      A       1     2021      74
 3      A       1     2022      72
 4      A       2     2020      45
 5      A       2     2021      21
 6      A       2     2022       5
 7      A       3     2020       4
 8      A       3     2021       9
 9      A       3     2022      10