topic Re: How to arrange dates using proc transpose and adding the total? in SAS Procedures

How to arrange dates using proc transpose and adding the total?

mrafael03 — Thu, 13 Aug 2020 23:18:50 GMT

Hello,

I have a data from the last 12 months and need help on how to transpose the date in order, group by IDs and getting the total count. Please advise

Version - SAS M6

See sample dataset below:

data dataset;
input id $1-3 yrmth 5-10 count 12;
datalines;
001 201911 2
001 201912 1
001 201903 1
001 202004 5
001 202006 1
002 201908 1
002 201909 2
002 201909 1
002 201910 1
003 202001 4
003 202002 3
003 202005 2
003 202007 1;
run;

EXPECTED RESULT:

ID 201908 201909 201910 201911 201912 202001 202002 202003 202004 202005 202006 202007 TOTAL

001 0 0 0 2 1 0 0 1 5 0 1 0 10

002 1 3 1 0 0 0 0 0 0 0 0 0 5

003 0 0 0 0 0 4 3 0 0 2 0 1 10

Re: How to arrange dates using proc transpose and adding the total?

PaigeMiller — Thu, 13 Aug 2020 23:21:34 GMT

Do you want the result to be a SAS data set, or do you want it to be a report?

Re: How to arrange dates using proc transpose and adding the total?

mrafael03 — Thu, 13 Aug 2020 23:24:09 GMT

It should be a dataset since i need to do a linkage on another table to create a summary report

Re: How to arrange dates using proc transpose and adding the total?

PaigeMiller — Thu, 13 Aug 2020 23:28:52 GMT

I'm skeptical that creating a wide data set from a long data set is a good idea ... usually, it is not a good idea and makes your coding harder. So please explain further this "linkage on another table".

Re: How to arrange dates using proc transpose and adding the total?

mrafael03 — Thu, 13 Aug 2020 23:37:21 GMT

From my first data set I have the data from the last 12 months

Variable includes:

ID
YEARMONTH
COUNT

Then, I'll do a linkage between member info table that includes:

ID
Member Demographics

FINAL Report will include

ID, Member Demographics, YearMonth (12 months), Total Count

SAMPLE OUTPUT

ID Name Phone 201908 201909 201910 201911 201912 202001 2019...... TOTAL

001 Ana 888##### 0 1 0 3 0 0 0 4

Re: How to arrange dates using proc transpose and adding the total?

PaigeMiller — Fri, 14 Aug 2020 00:08:50 GMT

So, again the idea of transposing to a wide data set seems unnecessary to me.

UNTESTED CODE

data dataset;
input id $1-3 yrmth 5-10 count 12;
datalines;
001 201911 2
001 201912 1
001 201903 1
001 202004 5
001 202006 1
002 201908 1
002 201909 2
002 201909 1
002 201910 1
003 202001 4
003 202002 3
003 202005 2
003 202007 1
;
proc sql;
	create table two as select a.*,b.name,b.phone
		from dataset a left join demographics b
		on a.id=b.id;
quit;
proc report data=two;
    columns id name phone yrmth,count count=total;
    define id / group;
    define name / group;
    define phone / group;
    define yrmth / across;
    define count / sum;
    define total / sum;
run;

Re: How to arrange dates using proc transpose and adding the total?

mrafael03 — Fri, 14 Aug 2020 00:49:48 GMT

I think I have a wrong approach. Tried the code and I think that's not what I'm looking for.

So basically, I have the 1st dataset and my goal is to add it on the existing table that consist of member table. I'm not sure on how I will add the yearmonth in order plus the total without hard coding it.

e.g.
proc sql;
create table test as select
a.id, a.fullname, b.aug2019, b.sept2019, b.oct2019, b.nov2019, b.dec2019,b.jan2020,
b.feb2020, b.mar2020, b.apr2020, b.may2020, b.jun2020, b.july2020,
(b.aug2019 + b.sept2019 + b.oct2019 + b.nov2019 + b.dec2019 + b.jan2020 +
b.feb2020 + b.mar2020 + b.apr2020 + b.may2020 + b.jun2020, b.july2020) as total label='total counts'
from member_tbl a
left join dataset1 b on a.id=b.id
order by id
;
QUIT;

Re: How to arrange dates using proc transpose and adding the total?

PaigeMiller — Fri, 14 Aug 2020 01:04:51 GMT

Now you see the problem with wide datasets and how difficult they are to work with.

The code I provided ought to work, or something similar ought to work, kbut you don't explain why you don't like it.

Re: How to arrange dates using proc transpose and adding the total?

mrafael03 — Fri, 14 Aug 2020 01:19:31 GMT

so i’m looking for a result that will automatically populate the year month accordingly. It might be a combination of transpose + macro?

Re: How to arrange dates using proc transpose and adding the total?

BrunoMueller — Fri, 14 Aug 2020 06:16:46 GMT

I agree with @PaigeMiller about the discussion wide versus long.

But if you want to go this way, here is a sample that gets you close. Please note, SAS does not allow for variable names to start with a digit by default. You have to set SAS System Option VALIDVARNAME=ANY. This then also means you have to use a name literal for those new names like so '201909'n.

data dataset;
  input id $1-3 yrmth 5-10 count 12;
  datalines;
001 201911 2
001 201912 1
001 201903 1
001 202004 5
001 202006 1
002 201908 1
002 201909 2
002 201909 1
002 201910 1
003 202001 4
003 202002 3
003 202005 2
003 202007 1
;

/*
 * create orderd lit of column names
 */
proc sql noprint;
  select distinct
    cats("'", yrmth, "'n")
  into
    :yrmth_list separated by " "
  from
    dataset
  order by
    1
  ;
quit;

%put NOTE: &=yrmth_list;

/*
 * create an empty base table
 * using the list from above
 */
data basetable;
  length
    id $ 3
    &yrmth_list 8
  ;
  stop;
run;

/*
 * aggregate data, since some yrmth appear more tan once
 */
proc sql;
  create table dataset_aggr as
    select
      id
      , yrmth
      , sum(count) as count
    from
      dataset
    group by
      id
      , yrmth
  ;
quit;

/*
 * transpose the data
 */
proc transpose
  data=dataset_aggr
  out=ds_trsp(drop=_name_)
;
  by id;
  id yrmth;
  var count;
run;

/*
 * append t base table
 */
proc append
  base=basetable
  data=ds_trsp
;
run;

Re: How to arrange dates using proc transpose and adding the total?

PaigeMiller — Fri, 14 Aug 2020 12:05:16 GMT

@mrafael03 wrote:
so i’m looking for a result that will automatically populate the year month accordingly. It might be a combination of transpose + macro?

PROC REPORT will do this easily without macro and without a transpose. You still haven't specified what was wrong with the results from the PROC REPORT I showed, and its possible that a relatively minor change to the above code will do what you want.

Re: How to arrange dates using proc transpose and adding the total?

PaigeMiller — Fri, 14 Aug 2020 11:05:02 GMT

@BrunoMueller thank you for confirming my point of view. Your code takes a lot more effort than PROC REPORT from a long data set.

Re: How to arrange dates using proc transpose and adding the total?

BrunoMueller — Fri, 14 Aug 2020 11:17:19 GMT

Totally agree Proc REPORT would be far less code, but I guess it all depends on what type of output is needed, report, data, ...

Re: How to arrange dates using proc transpose and adding the total?

Tom — Fri, 14 Aug 2020 11:53:47 GMT

There is a function for converting names into name literals. Of course you still need to convert the number into a string.

 nliteral(cats(yrmth))

Re: How to arrange dates using proc transpose and adding the total?

mrafael03 — Fri, 14 Aug 2020 15:44:04 GMT

Thanks for the sample code @BrunoMueller really appreciate it.

I have these codes on my original script. What I'm trying to achieve is on how I will include the 12 month period "YEARMONTH" on my final report without hard coding it as shown above

/*aggregate data, since some yrmth appear more tan once*/ proc sql; create table dataset_aggr as select id ,yrmth ,sum(count) as count from dataset group by id,yrmth; quit; /*transpose the data*/ proc transpose data=dataset_aggr out=ds_trsp(drop=_name_); by id; id yrmth; var count; run;

Re: How to arrange dates using proc transpose and adding the total?

Tom — Fri, 14 Aug 2020 16:06:58 GMT

Either create the total as a separate row in the TALL dataset.

proc sql;
create table dataset_aggr as select
 id
,cats(yrmth) as name length=6
,sum(count) as count
from dataset
group by id,yrmth
union
select
 id
,'TOTAL' as name length=6
,sum(count) as count
from dataset
group by id
;
quit;
...
  id name;
...

Or create the total in the WIDE dataset.

The second will be easy if ID is character.

data want;
  set ds_trsp;
  total=sum(of _numeric);
run;

Or make your transposed dataset have rational and uniquely prefixed valid SAS names by using the PREFIX= option on PROC TRANSPOSE so you can use a prefixed variable list in the SUM() function call.

proc transpose prefix=YYMM_ ....
...
  total=sum(of YYMM_:);
...

Re: How to arrange dates using proc transpose and adding the total?

mrafael03 — Fri, 14 Aug 2020 18:48:11 GMT

Please see attached expected output. Hopefully this is helpful

Re: How to arrange dates using proc transpose and adding the total?

mrafael03 — Fri, 14 Aug 2020 19:01:11 GMT

@PaigeMiller wrote:

Now you see the problem with wide datasets and how difficult they are to work with.

The code I provided ought to work, or something similar ought to work, kbut you don't explain why you don't like it.

Hi, again I tried your codes and it's actually worked and closer to what I'm looking for. Please see attached file for sample output that I'm aiming for

Re: How to arrange dates using proc transpose and adding the total?

BrunoMueller — Tue, 18 Aug 2020 20:01:44 GMT

Your Proc REPORT is very close to what you want, just some minor changes:

In the COLUMN statement you can either use acrossVar, analysisVar or analysisVar, acrossVar, see below
add an empty column label, this will suppress the printing
adding the statement options missing="0"; before Proc REPORT will print a 0 instead of ., please set it back to . after Proc REPORT

You can send the output directly to Excel using ODS EXCEL, as it looks like you want to have it in Excel.


proc report data=two;
    columns id name age count, yrmth count=total;
    define id / group;
    define name / group;
    define age / group;
    define yrmth / across " ";
    define count / sum " ";
    define total / sum "Total";
run;