Hello,
I'm trying to create a new column for every month of the year from values in a date column which is formatted as follows...
Date:
01/01/2008
02/01/2008
03/01/2008
.
.
.
10/01/2019
11/01/2019
12/01/2019
So every month of the year from '08-'19.
I would like all values of January to be in one columns, as well as columns for all the other individual months so that I can see the individual effects each month has on expenditure in a regression.
I thought that I might be able to search the date columns for the "MM/" part of MM/DD/YY and ignore the rest with a wildcard, then add those values to a new column called "MM", but I didn't have any luck.
I'm very new to SAS and might not have even known if I was searching for the right question, so apologies if this has been answered elsewhere.
I assume there are other variables that we need to make use of when we re-arrange the data set.
Can you show us a more realistic input data set, and the desired output data set? In particular, this isn't clear to me: "I would like all values of January to be in one columns". Do you mean there is one January column regardless of year, or is there a Jan08 column and a Jan09 column and so on?
You are correct, I would like for there to be a single January column containing different 'Total' values, a single column for February and so on.
1) Is your data in external file or a sas dataset ?
If it is external then the date is probably a CHAR type.
What format is the date: DD/MM/YYYY or MM/DD/YYYY ? It is not clear from your example dates.
If it is a sas dataset, is the date a sas numeric date ? Maybe what you see is formated display ?
2) why do you want each month in separate column ?
Do you want it in a report ? or in a new sas dataset?
3) Post a sample of your dataset in a INFILE DATALINES step, and post it in the running men icon window.
Post your code that you tried to run, the log and what issues did you have.
1) I imported the data from an excel file and I'm calling on that SAS table that was created. In the SAS table it says the input is in MMDDYY10. format.
2) I would like to be able to regress expenditures on individual months.
Expenditure= Int + Jan + Feb + Mar +...+ Nov + residuals
The same data set is fine.
3)
I used the import wizard rather than self input code.
DATA WORK.SupportGoods;
LENGTH
FY $ 6
MOS 8
Type 8
Eligible 8
PC_12MoAVG 8
Total 8
TotNoEFC 8
PCNoEFC_12MoAVG 8
FYTotals 8
FYEligible 8
FYPerCap 8
FYTotal 8
FYTotNoEFC 8
FYPC_NoEFC 8 ;
FORMAT
FY $CHAR6.
MOS MMDDYY10.
Type BEST4.
Eligible BEST11.
PC_12MoAVG BEST11.
Total BEST11.
TotNoEFC BEST11.
PCNoEFC_12MoAVG BEST11.
FYTotals BEST4.
FYEligible BEST11.
FYPerCap BEST11.
FYTotal BEST11.
FYTotNoEFC BEST11.
FYPC_NoEFC BEST11. ;
INFORMAT
FY $CHAR6.
MOS MMDDYY10.
Type BEST4.
Eligible BEST11.
PC_12MoAVG BEST11.
Total BEST11.
TotNoEFC BEST11.
PCNoEFC_12MoAVG BEST11.
FYTotals BEST4.
FYEligible BEST11.
FYPerCap BEST11.
FYTotal BEST11.
FYTotNoEFC BEST11.
FYPC_NoEFC BEST11. ;
INFILE 'C:\Users\doweljd\AppData\Local\Temp\SEG1848\SupportGoods-0b48c160721c43eda7d6c60c32977a3c.txt'
LRECL=143
ENCODING="WLATIN1"
TERMSTR=CRLF
DLM='7F'x
MISSOVER
DSD ;
INPUT
FY : $CHAR6.
MOS : ?? MMDDYY9.
Type : ?? BEST4.
Eligible : ?? COMMA11.
PC_12MoAVG : ?? COMMA11.
Total : ?? COMMA11.
TotNoEFC : ?? COMMA11.
PCNoEFC_12MoAVG : ?? COMMA11.
FYTotals : ?? BEST4.
FYEligible : ?? COMMA11.
FYPerCap : ?? COMMA11.
FYTotal : ?? COMMA11.
FYTotNoEFC : ?? COMMA11.
FYPC_NoEFC : ?? COMMA11. ;
RUN;
This was my idea of what would have created a new column for January if the date contained a substring for the month, but in retrospect if SAS stores it as a number I won't be able to search for a string value.
data s_g;
set sg;
if mos in ('01/') then Jan=Total;
if mos gt '01nov2018'd then
delete;
keep mos total;
run;
The log doesn't give any errors, but the output data doesn't give anything either.
@JDDowell wrote:
Hello,
I'm trying to create a new column for every month of the year from values in a date column which is formatted as follows...
Date:
01/01/2008
02/01/2008
03/01/2008
.
.
.
10/01/2019
11/01/2019
12/01/2019
So every month of the year from '08-'19.
I would like all values of January to be in one columns, as well as columns for all the other individual months so that I can see the individual effects each month has on expenditure in a regression.
I thought that I might be able to search the date columns for the "MM/" part of MM/DD/YY and ignore the rest with a wildcard, then add those values to a new column called "MM", but I didn't have any luck.
I'm very new to SAS and might not have even known if I was searching for the right question, so apologies if this has been answered elsewhere.
Is this for a report that people read or do you need a data set? Generally Reports show this sort of across behavior while data sets maintain a single record per date.
The more I talk about it the less certain I'm getting about if I'm even going about my question in the right way, but I'll try to explain my reasoning anyway.
My belief is that certain months have higher expenditures (the totals column) than others, and that this is consistent over the years. If I regress across the entire timeline then it is my understanding that I'm just going to forecast an average increase, and I won't be able to show these seasonal jumps that I perceive.
@JDDowell wrote:
The more I talk about it the less certain I'm getting about if I'm even going about my question in the right way, but I'll try to explain my reasoning anyway.
My belief is that certain months have higher expenditures (the totals column) than others, and that this is consistent over the years. If I regress across the entire timeline then it is my understanding that I'm just going to forecast an average increase, and I won't be able to show these seasonal jumps that I perceive.
Do you have access to SAS/ETS? That does timeseries analysis of various flavors that account for things like seasonality (increased sales of snow skiing equipment in Fall vs spring for example).
Those procedures understand dates for such things and no reshaping should be needed though you may need to specify the type of interval for collection (monthly instead of daily for instance).
Unfortunately I do not think my agency has a license for SAS/ETS, but I will email our IT. I was able to use PROC ARIMA to do time series analysis and was able to identify and adjust the model to a fair degree, but I don't entirely believe that I'm capturing the seasonal effect in my forecasted values. So I was trying to just do individual months.
I'm a few years removed from school, so my econometrics knowledge has degraded. Perhaps this is not the best way to tackle this issue.
I'm an idiot. I just realized I needed to change a parameter to get the desired monthly seasonality in my original ARIMA model. Thank you for your help.
@JDDowell wrote:
I'm an idiot. I just realized I needed to change a parameter to get the desired monthly seasonality in my original ARIMA model. Thank you for your help.
Proc Arima is in SAS/ETS, so apparently you have that module.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: