cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
Eugenio211
Quartz | Level 8 Eugenio211
Quartz | Level 8

Data Step / Query

Posted 06-29-2021 08:31 AM (866 views)

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
0 Likes
Reply
13 REPLIES 13
PaigeMiller
Diamond | Level 26 PaigeMiller
Diamond | Level 26

Re: Data Step / Query

Posted 06-29-2021 09:00 AM (855 views)  |   In reply to Eugenio211

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?

--
Paige Miller
Reply
Eugenio211
Quartz | Level 8 Eugenio211
Quartz | Level 8

Re: Data Step / Query

Posted 06-29-2021 09:05 AM (845 views)  |   In reply to PaigeMiller
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.
0 Likes
Reply
Eugenio211
Quartz | Level 8 Eugenio211
Quartz | Level 8

Re: Data Step / Query

Posted 06-29-2021 09:12 AM (835 views)  |   In reply to Eugenio211
The sample data I have came also from a data step. How do I run this without having the 'cards'? thanks
0 Likes
Reply
PaigeMiller
Diamond | Level 26 PaigeMiller
Diamond | Level 26

Re: Data Step / Query

Posted 06-29-2021 09:19 AM (828 views)  |   In reply to Eugenio211
@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!

--
Paige Miller
0 Likes
Reply
Eugenio211
Quartz | Level 8 Eugenio211
Quartz | Level 8

Re: Data Step / Query

Posted 06-29-2021 09:42 AM (808 views)  |   In reply to PaigeMiller
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.
0 Likes
Reply
PaigeMiller
Diamond | Level 26 PaigeMiller
Diamond | Level 26

Re: Data Step / Query

Posted 06-29-2021 09:44 AM (806 views)  |   In reply to Eugenio211

@Eugenio211 

 

you haven't shown us the output you want

--
Paige Miller
0 Likes
Reply
Eugenio211
Quartz | Level 8 Eugenio211
Quartz | Level 8

Re: Data Step / Query

Posted 06-29-2021 09:55 AM (801 views)  |   In reply to PaigeMiller

my bad, here is the sample output.

 

DM 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                                                     1 0                
John Smith Store 1 Feb 2021                                                         1 0            
John Smith Store 1 Mar 2021                                                             1 0        
John Smith Store 1 Apr 2021                                                                 1 0    
John Smith Store 1 Jan 2020                     834 396                                                
John Smith Store 1 Jul 2020                             295 47                                        
John Smith Store 1 Aug 2020                                 17 8                                    
John Smith Store 2 Sep 2020                                     13 3                                
Jane Doe Store 2 Oct 2020                                         230 12                            
Jane Doe Store 2 Nov 2020                                             113 14                        
Jane Doe Store 2 Dec 2020                                                 471 18                    
Jane Doe Store 2 Jan 2021                                                     533 31                
Jane Doe Store 2 Feb 2021                                                         22 8            
Jane Doe Store 2 Mar 2021                                                             503 16        
Jane Doe Store 2 Apr 2021                                                                 30 6    
Jane Doe Store 2 May 2021                                                                     458 14
Jane Doe Store 2 Jan 2020                     277 135                                                
Jane Doe Store 2 Jul 2020                             139 26                                        
John Brown Store 3 Aug 2020                                 15 4                                    
John Brown Store 3 Sep 2020                                     22 4                                
John Brown Store 3 Oct 2020                                         151 5                            
John Brown Store 3 Nov 2020                                             79 10                        
John Brown Store 3 Dec 2020                                                 288 16                    
John Brown Store 3 Jan 2021                                                     352 18                
John Brown Store 3 Feb 2021                                                         19 3            
John Brown Store 3 Mar 2021                                                             330 15        
John Brown Store 4 Apr 2021                                                                 29 7    
John Brown Store 4 May 2021                                                                     296 9
John Brown Store 4 Jan 2020                     593 263                                                
John Brown Store 4 Jul 2020                             324 40                                        
John Brown Store 4 Aug 2020                                 27 10                                    
Joe Blown Store 4 Sep 2020                                     19 2                                
Joe Blown Store 4 Oct 2020                                         280 13                            
Joe Blown Store 4 Nov 2020                                             107 4                        
Joe Blown Store 4 Dec 2020                                                 525 16                    
Joe Blown Store 4 Jan 2021                                                     576 18                
Joe Blown Store 4 Feb 2021                                                         27 2            
Joe Blown Store 4 Mar 2021                                                             529 12        
Joe Blown Store 5 Apr 2021                                                                 36 1    
Joe Blown Store 5 May 2021                                                                     511 10
Joe Blown Store 5 Jan 2020                     502 257                                                
Joe Blown Store 5 Jul 2020                             285 61                                        
Joe Blown Store 5 Aug 2020                                 13 6                                    
Joe Blown Store 5 Sep 2020                                     15 6                                
Joe Blown Store 5 Oct 2020                                         214 14                            
Joe Blown Store 5 Nov 2020                                             101 21                        
Wonder Woman Store 5 Dec 2020                                                 364 20                    
Wonder Woman Store 5 Jan 2021                                                     430 24                
Wonder Woman Store 5 Feb 2021                                                         12 2            
Wonder Woman Store 6 Mar 2021                                                             400 18        
Wonder Woman Store 6 Apr 2021                                                                 18 4    
Wonder Woman Store 6 May 2021                                                                     355 7
Wonder Woman Store 6 Jan 2020                     629 266                                                
Wonder Woman Store 6 Jul 2020                             339 51                                        
Wonder Woman Store 6 Aug 2020                                 18 7                                    
Wonder Woman Store 6 Sep 2020                                     18 5                                
Wonder Woman Store 6 Oct 2020                                         287 12                            
Wonder Woman Store 6 Nov 2020                                             146 11                        
Wonder Woman Store 6 Dec 2020                                                 576 35                    
Reply
PaigeMiller
Diamond | Level 26 PaigeMiller
Diamond | Level 26

Re: Data Step / Query

Posted 06-29-2021 10:19 AM (794 views)  |   In reply to Eugenio211

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.

--
Paige Miller
0 Likes
Reply
Eugenio211
Quartz | Level 8 Eugenio211
Quartz | Level 8

Re: Data Step / Query

Posted 06-29-2021 10:52 AM (782 views)  |   In reply to Eugenio211

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                    
0 Likes
Reply
PaigeMiller
Diamond | Level 26 PaigeMiller
Diamond | Level 26

Re: Data Step / Query

Posted 06-29-2021 11:05 AM (779 views)  |   In reply to Eugenio211

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.

--
Paige Miller
0 Likes
Reply
Eugenio211
Quartz | Level 8 Eugenio211
Quartz | Level 8

Re: Data Step / Query

Posted 06-29-2021 11:19 AM (774 views)  |   In reply to PaigeMiller
Thanks Paige.
0 Likes
Reply
Kurt_Bremser
Super User Kurt_Bremser
Super User

Re: Data Step / Query

Posted 06-29-2021 11:48 AM (765 views)  |   In reply to Eugenio211
@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.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX
0 Likes
Reply
Ksharp
Super User Ksharp
Super User

Re: Data Step / Query

Posted 06-29-2021 09:02 AM (849 views)  |   In reply to Eugenio211

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;
0 Likes
Reply

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Watch How to Concatenate Values on YouTube

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • ‎06-29-2021 08:31 AM
  • 867 views
  • 3 likes
  • 4 in conversation