BookmarkSubscribeRSS Feed
deleted_user
Not applicable
Dear all,

I have a problem about creating and modifying tables and views. For example :

Name Amount Month
Johnson 400000 01JAN2007
Michael 70000 04FEB2007
Williams 110000 07MAR2007
Morris 200000 08FEB2007



How to get the new table or output like this :
Name JAN FEB MAR
Johnson 400000 - -
Michael - 70000 -
Williams - - 110000
Morris - 200000 -

Thanks for your help.
6 REPLIES 6
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
You will need to explore using PROC TRANSPOSE against your data, and also since you have a DATE variable, you will need to investigate using INTNX to reset your DATE variable creating a MONTH_START_DATE variable, with an output FORMAT suitable to your display needs.

Scott Barry
SBBWorks, Inc.
andreas_lds
Jade | Level 19
Why INTNX? Using the MONTH-function seems to be easier.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
From my experience, it's best to keep your SAS date variable intact when possible. That's your decision though.

Also, I would not consider using the MONTH function at all -- reconsider an alternative approach where you assign a character variable to the first three characters of the month period using a PUT function and the SAS format MONNAME3. and also use the UPCASE function.

As usual, with the SAS language, several technical approaches exist to solve a particular problem or quest.

Scott Barry
SBBWorks, Inc.
deleted_user
Not applicable
data kamal;
input Name $ Salary Month;
datalines;
Johnson 400000 01JAN2007
Michael 70000 04FEB2007
Williams 110000 07MAR2007
Morris 200000 08FEB2007
;
run;

Create a Variable with the Month Number

Data kamal1;
set kamal;
MonthNum = Month(Month);
run;

Create a Array of Variables for all the months and input this data.

data kamal2;
set kamal1;
array a[12] Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec;
a[MonthNum] = Salary;
run;

I hope this solves the problem. However there might be a easy way of doing this also. I wish to see If anyone can do this in a easier way.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
As recommended - consider using MONNAME format. See pasted DOC link below.

Scott Barry
SBBWorks, Inc.

SAS Language Reference: Dictionary, MONNAMEw. Format
http://support.sas.com/documentation/cdl/en/lrdict/62618/HTML/default/a000201049.htm

NLS -
SAS National Language Support (NLS): Reference Guide, NLDATEMNw. Format
http://support.sas.com/documentation/cdl/en/nlsref/61893/HTML/default/a002603775.htm

General DOC link below on SAS Dates:

SAS Language Reference: Concepts, About SAS Date, Time, and Datetime Values
http://support.sas.com/documentation/cdl/en/lrcon/61722/HTML/default/a002200738.htm
deleted_user
Not applicable
Dear Kams,

Thanks a lot for your code, now the problem is done!

Thanks,
Meddy

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 6 replies
  • 851 views
  • 0 likes
  • 3 in conversation