topic Re: Date to Character in SAS Programming

Date to Character

Eugenio211 — Thu, 08 Sep 2022 18:06:52 GMT

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

Re: Date to Character

PeterClemmensen — Thu, 08 Sep 2022 18:13:16 GMT

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;

Re: Date to Character

PeterClemmensen — Thu, 08 Sep 2022 18:14:26 GMT

Also, why do you want a date as a character variable?

Re: Date to Character

Eugenio211 — Thu, 08 Sep 2022 18:22:32 GMT

Hi, I'm using it as a reference criteria in SUMIFS.

Thanks.

Re: Date to Character

PeterClemmensen — Thu, 08 Sep 2022 18:23:15 GMT

Ok. Did my code work for you?

Re: Date to Character

PaigeMiller — Thu, 08 Sep 2022 18:33:56 GMT

I don't see where SUMIFS works better on character string dates than on numeric dates.

Re: Date to Character

Eugenio211 — Thu, 08 Sep 2022 18:33:37 GMT

hello, I just need to convert so that all MONTH NAMES are in the same format.

Re: Date to Character

PaigeMiller — Thu, 08 Sep 2022 18:42:18 GMT

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

Re: Date to Character

Reeza — Thu, 08 Sep 2022 19:08:39 GMT

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?