Help using Base SAS procedures

Creating and modifying tables and views

Reply
N/A
Posts: 0

Creating and modifying tables and views

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.
Super Contributor
Super Contributor
Posts: 3,174

Re: Creating and modifying tables and views

Posted in reply to deleted_user
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.
Super Contributor
Posts: 345

Re: Creating and modifying tables and views

Why INTNX? Using the MONTH-function seems to be easier.
Super Contributor
Super Contributor
Posts: 3,174

Re: Creating and modifying tables and views

Posted in reply to andreas_lds
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.
N/A
Posts: 0

Re: Creating and modifying tables and views

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.
Super Contributor
Super Contributor
Posts: 3,174

Re: Creating and modifying tables and views

Posted in reply to deleted_user
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
N/A
Posts: 0

Re: Creating and modifying tables and views

Posted in reply to deleted_user
Dear Kams,

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

Thanks,
Meddy
Ask a Question
Discussion stats
  • 6 replies
  • 134 views
  • 0 likes
  • 3 in conversation