topic Creating and modifying tables and views in SAS Procedures

Creating and modifying tables and views

deleted_user — Thu, 24 Dec 2009 02:03:20 GMT

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.

Re: Creating and modifying tables and views

sbb — Thu, 24 Dec 2009 03:05:09 GMT

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.

Re: Creating and modifying tables and views

andreas_lds — Mon, 28 Dec 2009 08:14:55 GMT

Why INTNX? Using the MONTH-function seems to be easier.

Re: Creating and modifying tables and views

sbb — Mon, 28 Dec 2009 10:29:51 GMT

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.

Re: Creating and modifying tables and views

deleted_user — Mon, 28 Dec 2009 11:11:11 GMT

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.

Re: Creating and modifying tables and views

sbb — Mon, 28 Dec 2009 11:45:01 GMT

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

Re: Creating and modifying tables and views

deleted_user — Wed, 30 Dec 2009 04:01:48 GMT

Dear Kams,

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

Thanks,
Meddy