Create new variables based on observations to statistics

[Aleixo]

Hi,

Function transpose we don't have control on creation new variables, like name or create only new variables and organize them.

What i have is a table with a date refer to a name.

data WORK.Add_columns;
  input Nome:$300. Mes:$300.;
datalines4;
Joao 06M2017
Pedro 03M2016
Andre 05M2015
Miguel 01M2017
Jose 02M2016
Joao 03M2015
Pedro 05M2017
Andre 08M2014
Jose 12M2017
Jose 02M2016
Jose 02M2017
Joao 06M2017
Joao 06M2017
Joao 06M2017
Andre 05M2015
;;;;

What i want is a table like that. Organize for Month/Year in FORMAT=MMYY7.

It was dificult to understand how to do that in SAS. How to create new columns automatic with the observations values. I think the best is in a macro. One problem is how to create a new column and not a repeated column.

                 08M2014 03M2015 05M2015 02M2016 03M2016 01M2017 02M2017 05M2017 06M2017 12M2017 
Joao         0               1              0               0             0              0              0              0              4               0             
Pedro       0               0              0                0            1              0              0              1               0              0              
Andre       1               0              2               0            0              0               0             0               0               0           
Miguel      0               0               0               0            0              1               0              0              0               0            
Jose         0               0               0                2           0              0               1              0              0               1            

Regards,
Aleixo

[RW9]

The reason its difficult to do is because it is a very bad idea.  Variable names should be simple and easy to program with.  Giving them the names of dates - which are an abstract concept anyways - will make your programming life a nightmare.  In the worse case scenario, transpose the data up and give the variable names something like var, then apply the date as the label.

That being said, I really would not go for that data structure at all.  From data which is 15*2 datapoints, you are going with a 10*5 datapoints, so effectively creating 20 datapoints with no information - bad for storage, bad for processing.

[Aleixo]

The main idea is create a table with a statistics monthly.

This data is an example. The real life is different and not many zeros will appear in that statistic.

Ok a little bit different i can modify to:

- How to create a table automatic with columns Month/Year? For example for 4 ou 5 years sequentially based on today date to past dates?.

- After that creation i can update the values in that month/year  for each name, because with freq function give me the values.

It is a good idea?

@RW9 Thanks your answer

[RW9]

I really wouldn't recommend it.  Unless its for an output report (in which case tranpsoe before the report, or use an across statement), using normalised - data going down - is far easier to work with, smaller, and expandable endlessly.  E.g

MonthYear    Statistic   Result

...

This dataset, you can very easily add data to , filter the data, add other statistics, sort etc.  Try to do any of that with data going across, where you would need to find out what the variables are called, then loop across them, likely with big complicated messy macro code and arrays.

[Vish33]

you can create table with history data automatically using macro vars, but I am not sure if this is what you expect.

below program will get you the past 4 years data automatically if you mention the source data and date field i.e required to check.

data _null_;

call symput('dt',put(intnx('day',today(),0),mmyy7.));

call symput('dtb4y',put(intnx('year',today(),-4),mmyy7.));

run;

%put dt=&dt.;

%put dtb4y=&dtb4y.;

data want;

set have;

where actual_date between "&dtb4y."d and "&dt."d;

run;

[Aleixo]

Thanks your answer i will see that.

Sorry to question but the columns don't are organize by date. Variable is a format date and after tabulate function pass to numeric. 

Thank you very much your help

EDIT: Could be done if my date has this format yearmonth, like 201701, 201702, 201703 

[Vish33]

sort the date field "mes" in ascending order before transpose . this should give dates in sequence.

[Aleixo]

yes that's what i did.

Thanks

[Vish33]

Transpose wouldn't work as you have multiple same ID columns and would need unique value for ID column. But this can be done with cross-tabulation report with proc tabulate.

proc tabulate data=WORK.Add_columns;

class mes name;

tables name='',mes='' * n='' /misstext='0';

run;

Thanks,

Vish

[Aleixo]

Very good. 

It is possible to convert report to a sas7bdat? Or via excel and after convert to sas7bdat?

[Vish33 · Accepted solution]

yes, we can transpose the tabulate output to get the sas dataset.

proc tabulate data=WORK.Add_columns out= want;

class mes name;

tables name='',mes='' * n='' /misstext='0';

run;

proc transpose data=want out=want1(drop=_name_);

var n;

id mes;

by name;

run;