Rearranging table build

[cactooos]

Hi,

I am new to SAS programming, is there an easy way how can I rearrange the table below to get the build shown on the right?

[Duggins · Accepted solution]

Something like the following will work for the data you've shown. (In the future, it would help if you provided a working piece of code that includes the data you'd like to process.) The code I provided does make some assumptions about your data based on the fact that in the data you showed, you do not have duplicates of a date within the same value of Var1. If you expect duplicates, or your data set is otherwise different, you may find a DATA step-based approach more flexible.

data have;
  input var1 $ var2 var3 var4 Date $;
  cards;
abc 10 15 20 Feb-20
def 11 16 21 Feb-20
abc 12 16 21 Mar-20
xyz 10 17 23 Mar-20
  ;
run;

proc sort data = have;
  by var1 date;
run;

proc transpose data = have out = want(drop = _name_);
  by var1;
  id date;
  var var2 var3 var4;
run;

[ballardw]

First is to start with a SAS data set. Do you have one?

Since you did not show any actual values in the target table I'm guessing as to desired statistic may be needed

proc tabulated data=have;
   class var1  date  ;
   var var2 var3 var4;
   table var1 *(var2 var3 var4)*sum=' '* f=best5.,
         date
         /misstext=' '
   ;
run;

Note: if your dates are character they will likely not sort properly. And it is a poor idea to use 2 digit years, if that 20 is indeed a year.

It is best to make sure dates are SAS date valued numeric variables with an appropriate SAS date format assigned.

https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/... has a PDF with much information about dates.

[cactooos]

Thank you both, 
Yikes when I look at the solution it seems to be so easy to came up with but this is my 2nd month with SAS.
This is exactly what I was looking for. My data set is a table from proc sql grouped by var1 and date so no duplicated values expected.

Thanks

[ballardw]

---

@cactooos wrote:

Thank you both, 
Yikes when I look at the solution it seems to be so easy to came up with but this is my 2nd month with SAS.
This is exactly what I was looking for. My data set is a table from proc sql grouped by var1 and date so no duplicated values expected.

Thanks

---

Is the result from your Proc SQL used only to create the shown summary as a step to creating the desired table? If so you may want to share the Proc SQL code as it may not be needed at all. Proc Tabulate (and Proc Report) will do a lot of counts and groupings. The SQL may only be needed if you are getting the values from multiple data sets, using case statements to do fancy selection for counting or using a DISTINCT to reduce the data.

I am guilty of sometimes overusing one tool because I am more familiar with it even though another I have may be better. If you started with Proc SQL because of prior experience in SQL that may be the case.

Something to consider with Proc Tabulate: You can create multiple tables of different structures with one call to the procedure.

Proc tabulate data=sashelp.class;
   class sex age;
   var height weight;
   table sex,
          age
   ;
   Table sex*(height weight)*(min mean max),
         age
   ;
   table sex*age,
         (height weight) * (n mean std)
   ;
   table sex*height age*weight,
         n mean std
   ;
   table sex,
         age,
         (height weight) * (n mean std)
   ;
run;

As a not very sophisticated example. It does demonstrate that you can get different layouts. With more variable you could mix and match as (possibly) desired.

Caveat: Tabulate with missing values for ANY class variable will exclude the observation(s) affected unless the option MISSING is applied to the Class statement.

If you find yourself thinking about writing SQL code with a lot of Count(var) as varcount clauses you may want to investigate one of the other procedures. And Proc Tabulate and Report can create output data sets that admittedly take some getting used to work with. But the amount of other code that might be needed by other procedures can make it worthwhile. And when there are multiple tables as above the output dataset will contain a variable that indicates which table the rows of generated data are from.