DATA Step, Macro, Functions and more

Create new variables based on observations to statistics

Accepted Solution Solved
Reply
Contributor
Posts: 69
Accepted Solution

Create new variables based on observations to statistics

[ Edited ]

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


Accepted Solutions
Solution
‎05-04-2018 06:56 AM
Frequent Contributor
Posts: 142

Re: Create new variables based on observations to statistics

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;

View solution in original post


All Replies
Super User
Super User
Posts: 9,829

Re: Create new variables based on observations to statistics

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.

Contributor
Posts: 69

Re: Create new variables based on observations to statistics

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

Super User
Super User
Posts: 9,829

Re: Create new variables based on observations to statistics

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.

Frequent Contributor
Posts: 142

Re: Create new variables based on observations to statistics

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;

Contributor
Posts: 69

Re: Create new variables based on observations to statistics

[ Edited ]

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 

Frequent Contributor
Posts: 142

Re: Create new variables based on observations to statistics

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

Contributor
Posts: 69

Re: Create new variables based on observations to statistics

yes that's what i did.

 

Thanks

Frequent Contributor
Posts: 142

Re: Create new variables based on observations to statistics

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;

 

croos tab.JPG

 

Thanks,

Vish

Contributor
Posts: 69

Re: Create new variables based on observations to statistics

Very good. 

 

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

Solution
‎05-04-2018 06:56 AM
Frequent Contributor
Posts: 142

Re: Create new variables based on observations to statistics

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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 209 views
  • 3 likes
  • 3 in conversation