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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Vish33
Lapis Lazuli | Level 10

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

10 REPLIES 10
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Lapis Lazuli | Level 10

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
Quartz | Level 8

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
Lapis Lazuli | Level 10

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

Aleixo
Quartz | Level 8

yes that's what i did.

 

Thanks

Vish33
Lapis Lazuli | Level 10

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

Aleixo
Quartz | Level 8

Very good. 

 

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

Vish33
Lapis Lazuli | Level 10

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 10 replies
  • 1973 views
  • 3 likes
  • 3 in conversation