Hello, hope all is well.
I just want to ask help in regards to converting a date into character.
Below is the step I used, however I got blank dates after. Can someone tell me what I'm missing? thanks.
Data Step:
data Want; set Have;
Month_Date = intnx('month',input(put(Date,6. -L),yymmn6.),0,'E');
format Month_Date mmddyy.;
Month_Name = put(Month_Date,MONNAME3.);
run;
Data used:
Date | PAID_AMT | Brand | Pmt_Type | Channel | Business_State |
31Aug2022 | 18.62 | ABC | Paid | Internet | AB |
10Jun2022 | 0 | ABC | Paid | Internet | AB |
12Aug2022 | 0 | ABC | Paid | Internet | AB |
12Jan2022 | 62.85 | ABC | Paid | Internet | AB |
25Aug2022 | 43.13 | ABC | Paid | Internet | AB |
18Aug2022 | 0 | ABC | Paid | Internet | AB |
04Aug2022 | 0 | ABC | Paid | Internet | AB |
30Aug2022 | 32.41 | ABC | Paid | Internet | AB |
04Jul2022 | 0 | ABC | Paid | Internet | AB |
17Feb2022 | 29.92 | ABC | Paid | Internet | AB |
22Aug2022 | 69.6 | ABC | Paid | Internet | AB |
10Jun2022 | 0 | ABC | Paid | Internet | AB |
10Jun2022 | 0 | ABC | Paid | Internet | AB |
10Jun2022 | 0 | ABC | Paid | Internet | AB |
06Jul2022 | 0 | ABC | Paid | Internet | AB |
22Apr2022 | 0 | ABC | Paid | Internet | AB |
14Jul2022 | 0 | ABC | Paid | Internet | AB |
25Aug2022 | 62.85 | ABC | Paid | Internet | AB |
27Jun2022 | 0 | ABC | Paid | Internet | AB |
05Apr2022 | 0 | ABC | Paid | Internet | AB |
14Jul2022 | 62.42 | ABC | Paid | Internet | AB |
01Sep2022 | 0 | ABC | Paid | Internet | AB |
05May2022 | 44.03 | ABC | Paid | Internet | AB |
25Aug2022 | 18.37 | ABC | Paid | Internet | AB |
Result:
Date | PAID_AMT | Brand | Pmt_Type | Channel | Business_State | Month_Date | Month_Name |
31Aug2022 | 18.62 | ABC | Paid | Internet | AB | 08/31/2288 | Aug |
10Jun2022 | 0 | ABC | Paid | Internet | AB | 06/30/2280 | Jun |
12Aug2022 | 0 | ABC | Paid | Internet | AB | 09/30/2286 | Sep |
12Jan2022 | 62.85 | ABC | Paid | Internet | AB | 07/31/2265 | Jul |
25Aug2022 | 43.13 | ABC | Paid | Internet | AB | 02/29/2288 | Feb |
18Aug2022 | 0 | ABC | Paid | Internet | AB | 05/31/2287 | May |
04Aug2022 | 0 | ABC | Paid | Internet | AB | 01/31/2286 | Jan |
30Aug2022 | 32.41 | ABC | Paid | Internet | AB | 07/31/2288 | Jul |
04Jul2022 | 0 | ABC | Paid | Internet | AB | . | |
17Feb2022 | 29.92 | ABC | Paid | Internet | AB | 03/31/2269 | Mar |
22Aug2022 | 69.6 | ABC | Paid | Internet | AB | 09/30/2287 | Sep |
10Jun2022 | 0 | ABC | Paid | Internet | AB | 06/30/2280 | Jun |
10Jun2022 | 0 | ABC | Paid | Internet | AB | 06/30/2280 | Jun |
10Jun2022 | 0 | ABC | Paid | Internet | AB | 06/30/2280 | Jun |
06Jul2022 | 0 | ABC | Paid | Internet | AB | 02/28/2283 | Feb |
22Apr2022 | 0 | ABC | Paid | Internet | AB | 07/31/2275 | Jul |
14Jul2022 | 0 | ABC | Paid | Internet | AB | . | |
25Aug2022 | 62.85 | ABC | Paid | Internet | AB | 02/29/2288 | Feb |
27Jun2022 | 0 | ABC | Paid | Internet | AB | 03/31/2282 | Mar |
05Apr2022 | 0 | ABC | Paid | Internet | AB | . | |
14Jul2022 | 62.42 | ABC | Paid | Internet | AB | . | |
01Sep2022 | 0 | ABC | Paid | Internet | AB | 09/30/2288 | Sep |
05May2022 | 44.03 | ABC | Paid | Internet | AB | . | |
25Aug2022 | 18.37 | ABC | Paid | Internet | AB | 02/29/2288 | Feb |
Try this
data have;
input Date :date9. PAID_AMT Brand $ Pmt_Type $ Channel $ Business_State $;
format Date date9.;
datalines;
31Aug2022 18.62 ABC Paid Internet AB
10Jun2022 0 ABC Paid Internet AB
12Aug2022 0 ABC Paid Internet AB
12Jan2022 62.85 ABC Paid Internet AB
25Aug2022 43.13 ABC Paid Internet AB
18Aug2022 0 ABC Paid Internet AB
04Aug2022 0 ABC Paid Internet AB
30Aug2022 32.41 ABC Paid Internet AB
04Jul2022 0 ABC Paid Internet AB
17Feb2022 29.92 ABC Paid Internet AB
22Aug2022 69.6 ABC Paid Internet AB
10Jun2022 0 ABC Paid Internet AB
10Jun2022 0 ABC Paid Internet AB
10Jun2022 0 ABC Paid Internet AB
06Jul2022 0 ABC Paid Internet AB
22Apr2022 0 ABC Paid Internet AB
14Jul2022 0 ABC Paid Internet AB
25Aug2022 62.85 ABC Paid Internet AB
27Jun2022 0 ABC Paid Internet AB
05Apr2022 0 ABC Paid Internet AB
14Jul2022 62.42 ABC Paid Internet AB
01Sep2022 0 ABC Paid Internet AB
05May2022 44.03 ABC Paid Internet AB
25Aug2022 18.37 ABC Paid Internet AB
;
data want;
set have;
Month_Date = put(intnx('month', date, 0, 'e'), mmddyy10.);
Month_Name = put(date, monname3.);
run;
Also, why do you want a date as a character variable?
Ok. Did my code work for you?
I don't see where SUMIFS works better on character string dates than on numeric dates.
And then with character month names, APR is the first month of the year, AUG is the second month of the year, and so on ... is that really a good thing?
Or you can leave months as numeric, and then I'm sure there is a way to get Excel to sort them properly.
Or, you can create the table in SAS as I explained in your other thread leaving the dates as numeric, and then everything sorts properly, and then you can simply write the resulting table to Excel.
https://communities.sas.com/t5/SAS-Programming/Column-data-into-row-headers/m-p/832210
SUMIFS is Excel, is this something that isn't done more easily in SAS?
Also, are you using Pivot Tables or Slicers in your Excel which would be able to slice the data field into months/years/quarters dynamically?
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 25. 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.