Help transposing data.

[zouinenoah]

Hello,

I have a dataset with a CompanyCode variable, and a datetime variable DischargeFullDate for dates from January-July as shown for example in the photos below. I'm trying to find the % of people in each CompanyCode for every month so for example: The % of people in the month of January for CompanyCode 900, 901,......,etc. and transpose the data to look like

Company Code| Jan-21 | Feb-21 | Mar-21| etc.

901                   |     %     |      %    |     %    |

902

etc.

Company Codes exampleDatetime example

 

[Tom]

So you want a report of how many people per month?

What does the raw data look like?

[ballardw · Accepted solution]

Are you sure that you want a data set? A report that shows such things is likely to be much easier. Any data set in that sort of "wide" format gets to be awkward to work with for many analysis tasks.

Pseudo code for a report of what I think you are asking:

Proc tabulate data=have;
   class companycode dischargefulldate;
   format dischargefulldate dtmonyy7.;
   table companycode,
         dischargefulldate*rowpctn
  ;
run;

Groups created by formats will be honored in the reporting procedures like this and most analysis procedures.

If this is close then additional options can modify appearance a bit.

Note: Four-digit years really are a good idea.

[zouinenoah]

The table output that this gives, works perfect still. Is there a way to still include the company codes that have no values. Such as like CompanyCode 901 has no entries for all 7 months, is there a way to include it in the table even having no values?

[Reeza]

Yes, but you need to use a PRELOADFMT or add it as a record with all missing values to your main data set. Basically if the data does not exist, you do need to find some way to tell SAS that it does exist.

A few ways to do this are outlined here:

https://www.lexjansen.com/nesug/nesug11/cc/cc29.pdf

[ballardw]

---

@zouinenoah wrote:

The table output that this gives, works perfect still. Is there a way to still include the company codes that have no values. Such as like CompanyCode 901 has no entries for all 7 months, is there a way to include it in the table even having no values?

---

Yes. The easiest, IF you know all the codes that you need in the report is a custom format and some options in the Proc Tabulate code.

The format would look something like:

Proc format;
value $companycode
'901'= '901'
'902'= '902'
'903'= '903'
;

If you have a data set with the codes that can be used to create a format, which may be the better course in the long run but the example as above , with all the codes you want to appear, would be the basis. I am assuming, since no actual data is available that the codes are character. If the values are actually numeric modify the above code by removing the $ on the value statement and the quotes around the values on the left of the equal sign: i.e. 901 = '901'

Then in the Proc Tabulate code:

Proc tabulate data=have;
   class companycode /preloadfmt order=data;
   format companycode $companycode. ;
   class dischargefulldate;
   format dischargefulldate dtmonyy7.;
   table companycode,
         dischargefulldate*rowpctn
  ;
run;

The option on the class statement PRELOADFMT says to use all the values of the assigned format. Caution: This option does not work with things like dates and times or continuous numeric formats, only  something with a fairly explicit list of values. The PRELOADFMT requires either the option data=order or the table option PRINTMISS. Look in the Tabulate documentation for more details.

Note that you can have multiple Class statements to provide different options for different variables. The Class variables provide row and column headers.

[zouinenoah]

Thank you! I just had to add the /printmiss after the DischargeFullDate*rowpctn to display them in the table.