topic Re: Joining Tables in New SAS User

Joining Tables

Ryan199900 — Wed, 30 Mar 2022 19:22:45 GMT

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

Re: Joining Tables

PaigeMiller — Wed, 30 Mar 2022 19:46:19 GMT

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?

Re: Joining Tables

Kurt_Bremser — Wed, 30 Mar 2022 19:45:28 GMT

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

Re: Joining Tables

Tom — Wed, 30 Mar 2022 19:52:10 GMT

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