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

I have the following dataset.

data input;

input desc $2. tran_date date9. cnt 3. amt 3.;

format tran_date date9.;

datalines;

A 13Feb2015 2 10

A 10Jan2015 1 10

A 09Jan2015 1 20

B 05Feb2014 2 10

B 05Dec2014 2 20

B 02Dec2014 1 20

C 20Feb2015 1 10

C 31Jan2015 2 10

C 10Dec2014 2 20

C 10Dec2014 1 20

;

and the desired output is as follows -

sas.png


The month sums that I need to take is from 201308 to current month which results in a big query, if I try to do it with sql.

Can I do it datastep and macros ?

I would like to have an option to drop Dec2014_Cnt and take only the Dec2014_Amt alon with 4 other fields. Kindly advice.

1 ACCEPTED SOLUTION

Accepted Solutions
Vish33
Lapis Lazuli | Level 10

try this code:

data inpt (drop=tran_date);
set input;
new_var=put(intnx('month',tran_date,0),monname3.)||put(year(tran_date),4.);
run;  

proc sort data=inpt ; by desc new_var ; run;

proc tabulate data=inpt ;

class desc new_var;

var cnt amt;

tables desc='',new_var='' *(cnt amt)*sum=''/Box=Desc;

run;

View solution in original post

5 REPLIES 5
ballardw
Super User

Do you actually need a data set or a report in this layout?

rajat051984
Fluorite | Level 6

Sorry, I got your question wrong earlier. I need a report. The key field is not Desc but 6 other fields. But the count, amount layout is the one I mentioned. As you can figure it ,the report length will increase by two columns every month,

Message was edited by: rajat panda

Reeza
Super User

You don't store data in this format but you report it using proc report in this format -- or proc tabulate.

If you absolutely do need to store it this way then calculate the sums using proc means and then proc transpose the dataset.

Vish33
Lapis Lazuli | Level 10

try this code:

data inpt (drop=tran_date);
set input;
new_var=put(intnx('month',tran_date,0),monname3.)||put(year(tran_date),4.);
run;  

proc sort data=inpt ; by desc new_var ; run;

proc tabulate data=inpt ;

class desc new_var;

var cnt amt;

tables desc='',new_var='' *(cnt amt)*sum=''/Box=Desc;

run;

rajat051984
Fluorite | Level 6

Thank  you!!

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
  • 5 replies
  • 1037 views
  • 3 likes
  • 4 in conversation