topic Re: Data Step / Query in SAS Programming

Data Step / Query

Eugenio211 — Tue, 29 Jun 2021 12:31:14 GMT

Hello! hope everybody is doing great. I would like to ask your expertise and help on how to shorten the below query so as not to update this every month.

Data Have;
set Want;
if month(campaign_date)=2 and year(campaign_date)=2019 then do;
CAMPAIGN_MONTH = 'Feb 2019';
FEB19_CUSTOMERS = CUSTOMER_COUNT;
FEB19_CONVERTED = CONVERTED;
end;
else if month(campaign_date)=4 and year(campaign_date)=2019 then do;
CAMPAIGN_MONTH = 'Apr 2019';
APR19_CUSTOMERS = CUSTOMER_COUNT;
APR19_CONVERTED = CONVERTED;
end;
else if month(campaign_date)=7 and year(campaign_date)=2019 then do;
CAMPAIGN_MONTH = 'Jul 2019';
JUL19_CUSTOMERS = CUSTOMER_COUNT;
JUL19_CONVERTED = CONVERTED;
end;
else if month(campaign_date)=9 and year(campaign_date)=2019 then do;
CAMPAIGN_MONTH = 'Sep 2019';
SEP19_CUSTOMERS = CUSTOMER_COUNT;
SEP19_CONVERTED = CONVERTED;
end;
else if month(campaign_date)=10 and year(campaign_date)=2019 then do;
CAMPAIGN_MONTH = 'Oct 2019';
OCT19_CUSTOMERS = CUSTOMER_COUNT;
OCT19_CONVERTED = CONVERTED;
end;
else if month(campaign_date)=1 and year(campaign_date)=2020 then do;
CAMPAIGN_MONTH = 'Jan 2020';
JAN20_CUSTOMERS = CUSTOMER_COUNT;
JAN20_CONVERTED = CONVERTED;
end;
else if month(campaign_date)=6 and year(campaign_date)=2020 then do;
CAMPAIGN_MONTH = 'Jun 2020';
JUN20_CUSTOMERS = CUSTOMER_COUNT;
JUN20_CONVERTED = CONVERTED;
end;
else if month(campaign_date)=7 and year(campaign_date)=2020 then do;
CAMPAIGN_MONTH = 'Jul 2020';
JUL20_CUSTOMERS = CUSTOMER_COUNT;
JUL20_CONVERTED = CONVERTED;
end;
else if month(campaign_date)=8 and year(campaign_date)=2020 then do;
CAMPAIGN_MONTH = 'Aug 2020';
AUG20_CUSTOMERS = CUSTOMER_COUNT;
AUG20_CONVERTED = CONVERTED;
end;
else if month(campaign_date)=9 and year(campaign_date)=2020 then do;
CAMPAIGN_MONTH = 'Sep 2020';
SEP20_CUSTOMERS = CUSTOMER_COUNT;
SEP20_CONVERTED = CONVERTED;
end;
else if month(campaign_date)=10 and year(campaign_date)=2020 then do;
CAMPAIGN_MONTH = 'Oct 2020';
OCT20_CUSTOMERS = CUSTOMER_COUNT;
OCT20_CONVERTED = CONVERTED;
end;
else if month(campaign_date)=11 and year(campaign_date)=2020 then do;
CAMPAIGN_MONTH = 'Nov 2020';
NOV20_CUSTOMERS = CUSTOMER_COUNT;
NOV20_CONVERTED = CONVERTED;
end;
else if month(campaign_date)=12 and year(campaign_date)=2020 then do;
CAMPAIGN_MONTH = 'Dec 2020';
DEC20_CUSTOMERS = CUSTOMER_COUNT;
DEC20_CONVERTED = CONVERTED;
end;
else if month(campaign_date)=12 and year(campaign_date)=2020 then do;
CAMPAIGN_MONTH = 'Dec 2020';
DEC20_CUSTOMERS = CUSTOMER_COUNT;
DEC20_CONVERTED = CONVERTED;
end;
else if month(campaign_date)=1 and year(campaign_date)=2021 then do;
CAMPAIGN_MONTH = 'Jan 2021';
JAN21_CUSTOMERS = CUSTOMER_COUNT;
JAN21_CONVERTED = CONVERTED;
end;
else if month(campaign_date)=2 and year(campaign_date)=2021 then do;
CAMPAIGN_MONTH = 'Feb 2021';
FEB21_CUSTOMERS = CUSTOMER_COUNT;
FEB21_CONVERTED = CONVERTED;
end;
else if month(campaign_date)=3 and year(campaign_date)=2021 then do;
CAMPAIGN_MONTH = 'Mar 2021';
MAR21_CUSTOMERS = CUSTOMER_COUNT;
MAR21_CONVERTED = CONVERTED;
end;
else if month(campaign_date)=4 and year(campaign_date)=2021 then do;
CAMPAIGN_MONTH = 'Apr 2021';
APR21_CUSTOMERS = CUSTOMER_COUNT;
APR21_CONVERTED = CONVERTED;
end;
else if month(campaign_date)=5 and year(campaign_date)=2021 then do;
CAMPAIGN_MONTH = 'May 2021';
MAY21_CUSTOMERS = CUSTOMER_COUNT;
MAY21_CONVERTED = CONVERTED;
end;
drop CUSTOMER_COUNT CONVERTED campaign_date;
run;

Below is the data sample:

DM	LOCATION	CAMPAIGN_DATE	CUSTOMER_COUNT	CONVERTED
John Smith	Store 1	01/26/2021	1	0
John Smith	Store 1	02/24/2021	1	0
John Smith	Store 1	03/18/2021	1	0
John Smith	Store 1	04/20/2021	1	0
John Smith	Store 1	01/08/2020	834	396
John Smith	Store 1	07/17/2020	295	47
John Smith	Store 1	08/20/2020	17	8
John Smith	Store 2	09/25/2020	13	3
Jane Doe	Store 2	10/22/2020	230	12
Jane Doe	Store 2	11/11/2020	113	14
Jane Doe	Store 2	12/09/2020	471	18
Jane Doe	Store 2	01/26/2021	533	31
Jane Doe	Store 2	02/24/2021	22	8
Jane Doe	Store 2	03/18/2021	503	16
Jane Doe	Store 2	04/20/2021	30	6
Jane Doe	Store 2	05/11/2021	458	14
Jane Doe	Store 2	01/08/2020	277	135
Jane Doe	Store 2	07/17/2020	139	26
John Brown	Store 3	08/20/2020	15	4
John Brown	Store 3	09/25/2020	22	4
John Brown	Store 3	10/22/2020	151	5
John Brown	Store 3	11/11/2020	79	10
John Brown	Store 3	12/09/2020	288	16
John Brown	Store 3	01/26/2021	352	18
John Brown	Store 3	02/24/2021	19	3
John Brown	Store 3	03/18/2021	330	15
John Brown	Store 4	04/20/2021	29	7
John Brown	Store 4	05/11/2021	296	9
John Brown	Store 4	01/08/2020	593	263
John Brown	Store 4	07/17/2020	324	40
John Brown	Store 4	08/20/2020	27	10
Joe Blown	Store 4	09/25/2020	19	2
Joe Blown	Store 4	10/22/2020	280	13
Joe Blown	Store 4	11/11/2020	107	4
Joe Blown	Store 4	12/09/2020	525	16
Joe Blown	Store 4	01/26/2021	576	18
Joe Blown	Store 4	02/24/2021	27	2
Joe Blown	Store 4	03/18/2021	529	12
Joe Blown	Store 5	04/20/2021	36	1
Joe Blown	Store 5	05/11/2021	511	10
Joe Blown	Store 5	01/08/2020	502	257
Joe Blown	Store 5	07/17/2020	285	61
Joe Blown	Store 5	08/20/2020	13	6
Joe Blown	Store 5	09/25/2020	15	6
Joe Blown	Store 5	10/22/2020	214	14
Joe Blown	Store 5	11/11/2020	101	21
Wonder Woman	Store 5	12/09/2020	364	20
Wonder Woman	Store 5	01/26/2021	430	24
Wonder Woman	Store 5	02/24/2021	12	2
Wonder Woman	Store 6	03/18/2021	400	18
Wonder Woman	Store 6	04/20/2021	18	4
Wonder Woman	Store 6	05/11/2021	355	7
Wonder Woman	Store 6	01/08/2020	629	266
Wonder Woman	Store 6	07/17/2020	339	51
Wonder Woman	Store 6	08/20/2020	18	7
Wonder Woman	Store 6	09/25/2020	18	5
Wonder Woman	Store 6	10/22/2020	287	12
Wonder Woman	Store 6	11/11/2020	146	11
Wonder Woman	Store 6	12/09/2020	576	35

Re: Data Step / Query

PaigeMiller — Tue, 29 Jun 2021 13:03:17 GMT

How about this:

Data Have;
    set Want;
    format campaign_date monyy7.;
run;

This makes your campaign_date appear as Feb2019 or Apr2019 or whatever the month/year combination is.

The whole idea of creating variables named FEB19_CUSTOMERS, APR19_CUSTOMERS, etc. where data (the month and year) is placed into a variable name, is a very poor idea, that makes your programming much more difficult, as you have seen. Do not do this. Data should be stored in variables, rather than variable names.

If you want a report (instead of a data set) where the column names might contain FEB19, you can get this easily from PROC REPORT with a lot less work than your existing code.

So, what will you be doing with this data as the next step? Are you doing some analysis, or are you trying to create a report?

Re: Data Step / Query

Ksharp — Tue, 29 Jun 2021 13:02:45 GMT

The simplest way is DOUBLE TRANSPOSE .

data have;
infile cards expandtabs truncover;
input DM & $20. LOCATION & $20.	CAMPAIGN_DATE : mmddyy10.	CUSTOMER_COUNT	CONVERTED;
format 	CAMPAIGN_DATE  mmddyy10.;
cards;
John Smith 	Store 1	 01/26/2021	1	0
John Smith 	Store 1	 02/24/2021	1	0
John Smith 	Store 1	 03/18/2021	1	0
John Smith 	Store 1	 04/20/2021	1	0
John Smith 	Store 1	 01/08/2020	834	396
John Smith 	Store 1	 07/17/2020	295	47
John Smith 	Store 1 	08/20/2020	17	8
John Smith 	Store 2	 09/25/2020	13	3
Jane Doe 	Store 2 	10/22/2020	230	12
Jane Doe 	Store 2 	11/11/2020	113	14
Jane Doe 	Store 2 	12/09/2020	471	18
Jane Doe 	Store 2	 01/26/2021	533	31
Jane Doe 	Store 2 	02/24/2021	22	8
Jane Doe 	Store 2	 03/18/2021	503	16
Jane Doe 	Store 2 	04/20/2021	30	6
Jane Doe 	Store 2 	05/11/2021	458	14
Jane Doe 	Store 2 	01/08/2020	277	135
Jane Doe 	Store 2	 07/17/2020	139	26
;
proc sort data=have ;by DM LOCATION CAMPAIGN_DATE ;run;
proc transpose data=have out=temp;
by DM LOCATION CAMPAIGN_DATE ;
format campaign_date monyy7.;
var CUSTOMER_COUNT CONVERTED;
run;
proc transpose data=temp out=want delimiter=_;
by DM LOCATION;
var col1;
id CAMPAIGN_DATE _NAME_;
run;

Re: Data Step / Query

Eugenio211 — Tue, 29 Jun 2021 13:05:01 GMT

Hi - it's part of a process to create a report. I just want to write a query that I don't have to update every month when there are new records available for the current month.

Re: Data Step / Query

Eugenio211 — Tue, 29 Jun 2021 13:12:19 GMT

The sample data I have came also from a data step. How do I run this without having the 'cards'? thanks

Re: Data Step / Query

PaigeMiller — Tue, 29 Jun 2021 13:42:30 GMT

@Eugenio211 wrote:
Hi - it's part of a process to create a report. I just want to write a query that I don't have to update every month when there are new records available for the current month.

PROC REPORT.

Don't create your own variables with month in the variable name. Don't even bother with a double-transpose. Since you didn't say exactly what this report should look like, here is an example (which can obviously be modified if it is not what you want). Again, no need to create column names with FEB20 in the variable name.

proc report data=have;
    columns dm location campaign_date,(customer_count converted);
    define dm/group;
    define location/group;
    define campaign_date/across format=monyy7. order=internal;
    define customer_count/sum;
    define converted/sum;
run;

Again, please note that in the above code, I do not specify months in any fashion by name (like FEB20). PROC REPORT figures out what months you have and labels the columns properly. So if the data changes, and different months are in your data set, you do not have to change the code!

Re: Data Step / Query

Eugenio211 — Tue, 29 Jun 2021 13:42:48 GMT

Hi - this is a good query, however I was looking for a query that will shorten the one I have but will also give me the same output as my original query. thanks.

Re: Data Step / Query

PaigeMiller — Tue, 29 Jun 2021 13:44:19 GMT

@Eugenio211

you haven't shown us the output you want

Re: Data Step / Query

Eugenio211 — Tue, 29 Jun 2021 13:55:41 GMT

my bad, here is the sample output.

LOCATION

CAMPAIGN_MONTH

FEB19_CUSTOMERS

FEB19_CONVERTED

APR19_CUSTOMERS

APR19_CONVERTED

JUL19_CUSTOMERS

JUL19_CONVERTED

SEP19_CUSTOMERS

SEP19_CONVERTED

OCT19_CUSTOMERS

OCT19_CONVERTED

JAN20_CUSTOMERS

JAN20_CONVERTED

JUN20_CUSTOMERS

JUN20_CONVERTED

JUL20_CUSTOMERS

JUL20_CONVERTED

AUG20_CUSTOMERS

AUG20_CONVERTED

SEP20_CUSTOMERS

SEP20_CONVERTED

OCT20_CUSTOMERS

OCT20_CONVERTED

NOV20_CUSTOMERS

NOV20_CONVERTED

DEC20_CUSTOMERS

DEC20_CONVERTED

JAN21_CUSTOMERS

JAN21_CONVERTED

FEB21_CUSTOMERS

FEB21_CONVERTED

MAR21_CUSTOMERS

MAR21_CONVERTED

APR21_CUSTOMERS

APR21_CONVERTED

MAY21_CUSTOMERS

MAY21_CONVERTED

John Smith

Store 1

Jan 2021

John Smith

Store 1

Feb 2021

John Smith

Store 1

Mar 2021

John Smith

Store 1

Apr 2021

John Smith

Store 1

Jan 2020

834

396

John Smith

Store 1

Jul 2020

295

John Smith

Store 1

Aug 2020

John Smith

Store 2

Sep 2020

Jane Doe

Store 2

Oct 2020

230

Jane Doe

Store 2

Nov 2020

113

Jane Doe

Store 2

Dec 2020

471

Jane Doe

Store 2

Jan 2021

533

Jane Doe

Store 2

Feb 2021

Jane Doe

Store 2

Mar 2021

503

Jane Doe

Store 2

Apr 2021

Jane Doe

Store 2

May 2021

458

Jane Doe

Store 2

Jan 2020

277

135

Jane Doe

Store 2

Jul 2020

139

John Brown

Store 3

Aug 2020

John Brown

Store 3

Sep 2020

John Brown

Store 3

Oct 2020

151

John Brown

Store 3

Nov 2020

John Brown

Store 3

Dec 2020

288

John Brown

Store 3

Jan 2021

352

John Brown

Store 3

Feb 2021

John Brown

Store 3

Mar 2021

330

John Brown

Store 4

Apr 2021

John Brown

Store 4

May 2021

296

John Brown

Store 4

Jan 2020

593

263

John Brown

Store 4

Jul 2020

324

John Brown

Store 4

Aug 2020

Joe Blown

Store 4

Sep 2020

Joe Blown

Store 4

Oct 2020

280

Joe Blown

Store 4

Nov 2020

107

Joe Blown

Store 4

Dec 2020

525

Joe Blown

Store 4

Jan 2021

576

Joe Blown

Store 4

Feb 2021

Joe Blown

Store 4

Mar 2021

529

Joe Blown

Store 5

Apr 2021

Joe Blown

Store 5

May 2021

511

Joe Blown

Store 5

Jan 2020

502

257

Joe Blown

Store 5

Jul 2020

285

Joe Blown

Store 5

Aug 2020

Joe Blown

Store 5

Sep 2020

Joe Blown

Store 5

Oct 2020

214

Joe Blown

Store 5

Nov 2020

101

Wonder Woman

Store 5

Dec 2020

364

Wonder Woman

Store 5

Jan 2021

430

Wonder Woman

Store 5

Feb 2021

Wonder Woman

Store 6

Mar 2021

400

Wonder Woman

Store 6

Apr 2021

Wonder Woman

Store 6

May 2021

355

Wonder Woman

Store 6

Jan 2020

629

266

Wonder Woman

Store 6

Jul 2020

339

Wonder Woman

Store 6

Aug 2020

Wonder Woman

Store 6

Sep 2020

Wonder Woman

Store 6

Oct 2020

287

Wonder Woman

Store 6

Nov 2020

146

Wonder Woman

Store 6

Dec 2020

576

Re: Data Step / Query

PaigeMiller — Tue, 29 Jun 2021 14:25:15 GMT

I don't understand. In your original code, CAMPAIGN_MONTH was computed from CAMPAIGN_DATE, meaning these two are identical, always. Now you want a column of FEB19_CUSTOMERS for CAMPAIGN_MONTH of Jan 2021. How can this be?

Please show us a portion of your original data, following these instructions; and not via a screen capture or file attachment.

Re: Data Step / Query

Eugenio211 — Tue, 29 Jun 2021 14:52:44 GMT

I am counting the number of Customers and Converted for each month and translating it to a column. it should be blank if there is no count for that CAMPAIGN_MONTH.

here is a new example, I added the Customer_Count and Converted columns. Thanks a lot.

DM	LOCATION	CAMPAIGN_DATE	CUSTOMER_COUNT	CONVERTED	CAMPAIGN_MONTH	FEB19_CUSTOMERS	FEB19_CONVERTED	APR19_CUSTOMERS	APR19_CONVERTED	JUL19_CUSTOMERS	JUL19_CONVERTED	SEP19_CUSTOMERS	SEP19_CONVERTED	OCT19_CUSTOMERS	OCT19_CONVERTED	JAN20_CUSTOMERS	JAN20_CONVERTED	JUN20_CUSTOMERS	JUN20_CONVERTED	JUL20_CUSTOMERS	JUL20_CONVERTED	AUG20_CUSTOMERS	AUG20_CONVERTED	SEP20_CUSTOMERS	SEP20_CONVERTED	OCT20_CUSTOMERS	OCT20_CONVERTED	NOV20_CUSTOMERS	NOV20_CONVERTED	DEC20_CUSTOMERS	DEC20_CONVERTED	JAN21_CUSTOMERS	JAN21_CONVERTED	FEB21_CUSTOMERS	FEB21_CONVERTED	MAR21_CUSTOMERS	MAR21_CONVERTED	APR21_CUSTOMERS	APR21_CONVERTED	MAY21_CUSTOMERS	MAY21_CONVERTED
John Smith	Store 1	01/26/2021	1	0	Jan 2021																											1	0
John Smith	Store 1	02/24/2021	1	0	Feb 2021																													1	0
John Smith	Store 1	03/18/2021	1	0	Mar 2021																															1	0
John Smith	Store 1	04/20/2021	1	0	Apr 2021																																	1	0
John Smith	Store 1	01/08/2020	834	396	Jan 2020											834	396
John Smith	Store 1	07/17/2020	295	47	Jul 2020															295	47
John Smith	Store 1	08/20/2020	17	8	Aug 2020																	17	8
John Smith	Store 2	09/25/2020	13	3	Sep 2020																			13	3
Jane Doe	Store 2	10/22/2020	230	12	Oct 2020																					230	12
Jane Doe	Store 2	11/11/2020	113	14	Nov 2020																							113	14
Jane Doe	Store 2	12/09/2020	471	18	Dec 2020																									471	18
Jane Doe	Store 2	01/26/2021	533	31	Jan 2021																											533	31
Jane Doe	Store 2	02/24/2021	22	8	Feb 2021																													22	8
Jane Doe	Store 2	03/18/2021	503	16	Mar 2021																															503	16
Jane Doe	Store 2	04/20/2021	30	6	Apr 2021																																	30	6
Jane Doe	Store 2	05/11/2021	458	14	May 2021																																			458	14
Jane Doe	Store 2	01/08/2020	277	135	Jan 2020											277	135
Jane Doe	Store 2	07/17/2020	139	26	Jul 2020															139	26
John Brown	Store 3	08/20/2020	15	4	Aug 2020																	15	4
John Brown	Store 3	09/25/2020	22	4	Sep 2020																			22	4
John Brown	Store 3	10/22/2020	151	5	Oct 2020																					151	5
John Brown	Store 3	11/11/2020	79	10	Nov 2020																							79	10
John Brown	Store 3	12/09/2020	288	16	Dec 2020																									288	16
John Brown	Store 3	01/26/2021	352	18	Jan 2021																											352	18
John Brown	Store 3	02/24/2021	19	3	Feb 2021																													19	3
John Brown	Store 3	03/18/2021	330	15	Mar 2021																															330	15
John Brown	Store 4	04/20/2021	29	7	Apr 2021																																	29	7
John Brown	Store 4	05/11/2021	296	9	May 2021																																			296	9
John Brown	Store 4	01/08/2020	593	263	Jan 2020											593	263
John Brown	Store 4	07/17/2020	324	40	Jul 2020															324	40
John Brown	Store 4	08/20/2020	27	10	Aug 2020																	27	10
Joe Blown	Store 4	09/25/2020	19	2	Sep 2020																			19	2
Joe Blown	Store 4	10/22/2020	280	13	Oct 2020																					280	13
Joe Blown	Store 4	11/11/2020	107	4	Nov 2020																							107	4
Joe Blown	Store 4	12/09/2020	525	16	Dec 2020																									525	16
Joe Blown	Store 4	01/26/2021	576	18	Jan 2021																											576	18
Joe Blown	Store 4	02/24/2021	27	2	Feb 2021																													27	2
Joe Blown	Store 4	03/18/2021	529	12	Mar 2021																															529	12
Joe Blown	Store 5	04/20/2021	36	1	Apr 2021																																	36	1
Joe Blown	Store 5	05/11/2021	511	10	May 2021																																			511	10
Joe Blown	Store 5	01/08/2020	502	257	Jan 2020											502	257
Joe Blown	Store 5	07/17/2020	285	61	Jul 2020															285	61
Joe Blown	Store 5	08/20/2020	13	6	Aug 2020																	13	6
Joe Blown	Store 5	09/25/2020	15	6	Sep 2020																			15	6
Joe Blown	Store 5	10/22/2020	214	14	Oct 2020																					214	14
Joe Blown	Store 5	11/11/2020	101	21	Nov 2020																							101	21
Wonder Woman	Store 5	12/09/2020	364	20	Dec 2020																									364	20
Wonder Woman	Store 5	01/26/2021	430	24	Jan 2021																											430	24
Wonder Woman	Store 5	02/24/2021	12	2	Feb 2021																													12	2
Wonder Woman	Store 6	03/18/2021	400	18	Mar 2021																															400	18
Wonder Woman	Store 6	04/20/2021	18	4	Apr 2021																																	18	4
Wonder Woman	Store 6	05/11/2021	355	7	May 2021																																			355	7
Wonder Woman	Store 6	01/08/2020	629	266	Jan 2020											629	266
Wonder Woman	Store 6	07/17/2020	339	51	Jul 2020															339	51
Wonder Woman	Store 6	08/20/2020	18	7	Aug 2020																	18	7
Wonder Woman	Store 6	09/25/2020	18	5	Sep 2020																			18	5
Wonder Woman	Store 6	10/22/2020	287	12	Oct 2020																					287	12
Wonder Woman	Store 6	11/11/2020	146	11	Nov 2020																							146	11
Wonder Woman	Store 6	12/09/2020	576	35	Dec 2020																									576	35

Re: Data Step / Query

PaigeMiller — Tue, 29 Jun 2021 15:07:46 GMT

I still don't understand, is this new table the desired new output? You want a humongous wide table where most entries are blank? This seems unnecessary and a poor layout.

In any event, I can't help further until you provide a portion of the original SAS data set, following the instructions given.

Re: Data Step / Query

Eugenio211 — Tue, 29 Jun 2021 15:19:22 GMT

Thanks Paige.

Re: Data Step / Query

Kurt_Bremser — Tue, 29 Jun 2021 15:48:23 GMT

@Eugenio211 wrote:
Hi - it's part of a process to create a report. I just want to write a query that I don't have to update every month when there are new records available for the current month.

For a report, YOU DO NOT NEED THIS DATASET!

As demonstrated, PROC REPORT can create a wide report from a long dataset on the fly.

If you need assistance in preparing a report from a proper laxout, we can provide that. Show us an example for the final report you expect.

Data does not belong in structure, see Maxim 19.