BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mrafael03
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
BrunoMueller
SAS Super FREQ

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;

View solution in original post

18 REPLIES 18
PaigeMiller
Diamond | Level 26

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

 
--
Paige Miller
mrafael03
Obsidian | Level 7
It should be a dataset since i need to do a linkage on another table to create a summary report
PaigeMiller
Diamond | Level 26

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".

 
--
Paige Miller
mrafael03
Obsidian | Level 7

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

Variable includes:

  1. ID
  2. YEARMONTH
  3. COUNT

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

  1. ID
  2. 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

PaigeMiller
Diamond | Level 26

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;

 

 

--
Paige Miller
mrafael03
Obsidian | Level 7
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;

PaigeMiller
Diamond | Level 26

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.

 
--
Paige Miller
mrafael03
Obsidian | Level 7
so i’m looking for a result that will automatically populate the year month accordingly. It might be a combination of transpose + macro?
PaigeMiller
Diamond | Level 26

@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.

--
Paige Miller
mrafael03
Obsidian | Level 7

@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

BrunoMueller
SAS Super FREQ

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;
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
BrunoMueller
SAS Super FREQ

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

Tom
Super User Tom
Super User

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))

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 18 replies
  • 1605 views
  • 3 likes
  • 4 in conversation