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: 
RobertNYC
‎05-13-2019
  • 111 Posts
  • 6 Likes Given
  • 0 Solutions
  • 1 Likes Received

Re: Tricky Join: Joining Two Data Sets Based On A Date Range With Aggr...

by Obsidian | Level 7 in SAS Programming
‎02-13-2014 03:11 PM
‎02-13-2014 03:11 PM
Perfect!  Thanks!  Last question:  how would you suggest I incorporate the two programs (yours and the one which sums the clicks  since  drop_date and beginDate will be the same in some situations? drop_date URL Creative_Name BeginDate 20-May-13 Link.com/east/2es Perf Offer 20-May-13 7-Mar-13 Link.com/north/2gq Bonuse Power 7-Mar-13 31-Jan-13 Link.com/south/2ke Offer Box 31-Jan-13 12-Aug-13 Link.com/south/2ke Sign Up Now 31-Jan-13 30-Sep-13 Link.com/west/2ev We Want Bonuses 30-Sep-13 data Creative_Drops_new; set Creative_Drops; by URL Creative_Name; if first.URL then do; BeginDate=.; if first.Creative_Name then do; if Drop_Date ne . then BeginDate=Drop_Date; if last.URL then do; if last.Creative_Name then do; retain BeginDate; format BeginDate date9.; end; end; end; end; run; proc sql;    create table analyze as    select a.*, b.ResponseDate,b.clicks    from Creative_Drops as a left join URL_Responses as b       on b.ResponseDate ge a.drop_date and a.url=b.url;    create table result as    select drop_date, URL, Creative_Name, sum(clicks) as Total_Clicks    from analyze    group by drop_date, URL, Creative_Name    ; quit;         ... View more

Re: Tricky Join: Joining Two Data Sets Based On A Date Range With Aggr...

by Obsidian | Level 7 in SAS Programming
‎02-13-2014 02:43 PM
‎02-13-2014 02:43 PM
Hi AndyK- I am getting all null values for DropDate and BeginDate not sure why? data Creative_Drops_new; set Creative_Drops; by URL Creative_Name; if first.URL then do; BeginDate=.; if first.Creative_Name then do; if DropDate ne . then BeginDate=DropDate; if last.URL then do; if last.Creative_Name then do; retain BeginDate; end; end; end; end; run; ... View more

Re: Tricky Join: Joining Two Data Sets Based On A Date Range With Aggr...

by Obsidian | Level 7 in SAS Programming
‎02-13-2014 01:32 PM
‎02-13-2014 01:32 PM
Hi AndyK. Thank you for your reply. Yes the Start and Stop Dates are the drop_dates. So if you look at the table below  where the creatives both use the same URL Sign Up Now has a Start Date of 12-Aug-13 and its End date is 31-Jan-13 which is the Start Date of Offer Box. I just don't know how to break out the start and stop dates since they are both contained in the same variable. drop_date URL Creative_Name ResponseDate Clicks 12-Aug-13 Link.com/south/2ke Sign Up Now 18-Jan-14 97 31-Jan-13 Link.com/south/2ke Offer Box 18-Jan-14 97 12-Aug-13 Link.com/south/2ke Sign Up Now 11-Jan-14 89 31-Jan-13 Link.com/south/2ke Offer Box 11-Jan-14 89 ... View more

Re: Tricky Join: Joining Two Data Sets Based On A Date Range With Aggr...

by Obsidian | Level 7 in SAS Programming
‎02-13-2014 01:18 PM
‎02-13-2014 01:18 PM
Hi Ballard! Thank you for your reply. I think the above is close.  The issue is I only want to Clicks to sum for each creative during the time range between the drop dates. A given URL can only be assigned to one creative at a given time.  So lets say Creative A and Creative B use the same URL but the can't use it at the same time.  Creative A has a drop date of 1/20/2013 I need to sum all the responses until Creative B is assigned to the URL on drop date 3/20/2013.  So the start date and end date are the drop dates for the creatives. Its looks like the all of the clicks are being summed regardless of when the url was assigned or unassigned. drop_date URL Creative_Name ResponseDate Clicks 12-Aug-13 Link.com/south/2ke Sign Up Now 18-Jan-14 97 31-Jan-13 Link.com/south/2ke Offer Box 18-Jan-14 97 12-Aug-13 Link.com/south/2ke Sign Up Now 11-Jan-14 89 31-Jan-13 Link.com/south/2ke Offer Box 11-Jan-14 89 ... View more

Tricky Join: Joining Two Data Sets Based On A Date Range With Aggregat...

by Obsidian | Level 7 in SAS Programming
‎02-13-2014 10:56 AM
‎02-13-2014 10:56 AM
Hi all— I'm trying to join a data set that contains a frequency of ‘Clicks’ on a ResponseDate for specific URLs to a data set which links the URL to the ‘Creative Name’ and ‘drop_date’ its associated with. I need to bring back a data set that sums the clicks by drop_date and URL for each ‘Creative Name’. This is an example of the final product I need: drop_date URL  Creative_Name Total_Clicks 5/20/2013 Link.com/east/2es Perf Offer 10 9/30/2013 Link.com/west/2ev We Want Bonuses 2 3/7/2013 Link.com/north/2gq Bonuse Power 2 1/31/2013 Link.com/south/2ke Offer Box 41 8/12/2013 Link.com/south/2ke Sign Up Now 21 The issue I’m having is if you notice some URLs (Link.com/south/2ke) are used more than once for Creative_Names .  I need sum all the clicks that have a Response_Date on or after the drop_date and not the clicks that happen when the same URL is used again for another Creative_Name. So the Creative_Name ‘Offer Box’  had a drop_date of 1/31/2013 and the link associated with it was ‘Link.com/south/2ke’  and this link was then assigned to a new Creative_Name Sign Up Now  on  8/12/2013. I have included two sample data sets URL_Responses and Creative_Drops. Any assistance is greatly appreciated!!! Thanks!!! data Creative_Drops; length drop_date 8 URL $20 Creative_Name $30; INPUT drop_date : anydtdte9.  URL $  Creative_Name $20.; format drop_date date9.; datalines; 5/20/2013 Link.com/east/2es Perf Offer 9/30/2013 Link.com/west/2ev We Want Bonuses 3/7/2013 Link.com/north/2gq Bonuse Power 1/31/2013 Link.com/south/2ke Offer Box 8/12/2013 Link.com/south/2ke Sign Up Now ;  data URL_Responses; length ResponseDate 8 URL $30 Clicks 4; INPUT ResponseDate : anydtdte9.  URL $  Clicks; format ResponseDate date9.;    datalines; 3/2/13 Link.com/south/2ke 1 4/6/13 Link.com/south/2ke 2 2/23/13 Link.com/south/2ke 32 5/11/13 Link.com/south/2ke 41 5/4/13 Link.com/south/2ke 21 6/22/13 Link.com/south/2ke 2 1/26/13 Link.com/south/2ke 4 2/9/13 Link.com/south/2ke 27 2/2/13 Link.com/south/2ke 2 1/19/13 Link.com/south/2ke 3 6/8/13 Link.com/south/2ke 3 6/15/13 Link.com/south/2ke 1 4/20/13 Link.com/south/2ke 9 3/9/13 Link.com/south/2ke 12 5/18/13 Link.com/south/2ke 19 8/17/13 Link.com/south/2ke 8 9/21/13 Link.com/south/2ke 4 5/25/13 Link.com/south/2ke 1 1/4/14 Link.com/south/2ke 19 9/14/13 Link.com/south/2ke 6 6/1/13 Link.com/south/2ke 4 6/29/13 Link.com/south/2ke 1 8/31/13 Link.com/south/2ke 57 8/3/13 Link.com/south/2ke 2 1/11/14 Link.com/south/2ke 89 1/18/14 Link.com/south/2ke 97 9/7/13 Link.com/south/2ke 53 12/28/13 Link.com/south/2ke 5 10/12/13 Link.com/south/2ke 5 2/1/14 Link.com/south/2ke 2 1/25/14 Link.com/south/2ke 11 2/16/13 Link.com/south/2ke 12 8/24/13 Link.com/south/2ke 51 9/28/13 Link.com/south/2ke 4 12/21/13 Link.com/south/2ke 4 3/30/13 Link.com/north/2gq 2 4/13/13 Link.com/north/2gq 1 5/11/13 Link.com/north/2gq 6 5/18/13 Link.com/north/2gq 2 6/22/13 Link.com/north/2gq 2 3/16/13 Link.com/north/2gq 29 3/9/13 Link.com/north/2gq 3 4/27/13 Link.com/north/2gq 4 6/1/13 Link.com/north/2gq 2 5/25/13 Link.com/north/2gq 2 1/4/14 Link.com/north/2gq 5 11/16/13 Link.com/north/2gq 2 12/7/13 Link.com/north/2gq 2 12/14/13 Link.com/north/2gq 1 1/18/14 Link.com/north/2gq 9 4/6/13 Link.com/north/2gq 1 12/1/12 Link.com/west/2ev 2 12/15/12 Link.com/west/2ev 9 1/12/13 Link.com/west/2ev 1 2/23/13 Link.com/west/2ev 1 3/30/13 Link.com/west/2ev 1 1/5/13 Link.com/west/2ev 3 11/24/12 Link.com/west/2ev 2 5/11/13 Link.com/west/2ev 62 12/29/12 Link.com/west/2ev 10 4/13/13 Link.com/west/2ev 93 5/4/13 Link.com/west/2ev 102 4/27/13 Link.com/west/2ev 184 6/15/13 Link.com/west/2ev 20 12/22/12 Link.com/west/2ev 11 6/1/13 Link.com/west/2ev 15 7/20/13 Link.com/west/2ev 4 3/9/13 Link.com/west/2ev 4 5/4/13 Link.com/west/2ev 2 7/27/13 Link.com/west/2ev 3 4/13/13 Link.com/west/2ev 78 8/24/13 Link.com/west/2ev 13 3/16/13 Link.com/west/2ev 1 8/31/13 Link.com/west/2ev 6 4/27/13 Link.com/west/2ev 5 6/29/13 Link.com/west/2ev 8 5/18/13 Link.com/west/2ev 31 4/20/13 Link.com/west/2ev 420 4/20/13 Link.com/west/2ev 39 9/28/13 Link.com/west/2ev 5 9/28/13 Link.com/west/2ev 5 8/3/13 Link.com/west/2ev 3 12/7/13 Link.com/west/2ev 10 6/22/13 Link.com/west/2ev 12 10/26/13 Link.com/west/2ev 13 7/6/13 Link.com/west/2ev 6 11/2/13 Link.com/west/2ev 1 5/25/13 Link.com/west/2ev 17 9/7/13 Link.com/west/2ev 18 8/10/13 Link.com/west/2ev 16 11/30/13 Link.com/west/2ev 4 10/5/13 Link.com/west/2ev 1 10/19/13 Link.com/west/2ev 34 11/23/13 Link.com/west/2ev 2 1/25/14 Link.com/west/2ev 2 8/24/13 Link.com/west/2ev 1 8/17/13 Link.com/west/2ev 7 9/14/13 Link.com/west/2ev 10 11/16/13 Link.com/west/2ev 3 10/12/13 Link.com/west/2ev 21 9/21/13 Link.com/west/2ev 1 10/5/13 Link.com/west/2ev 2 12/21/13 Link.com/west/2ev 1 2/1/14 Link.com/west/2ev 2 7/14/12 Link.com/west/2ev 2 7/28/12 Link.com/east/2es 10 9/22/12 Link.com/east/2es 1 7/14/12 Link.com/east/2es 23 8/11/12 Link.com/east/2es 1 12/1/12 Link.com/east/2es 9 12/15/12 Link.com/east/2es 278 8/18/12 Link.com/east/2es 2 1/19/13 Link.com/east/2es 15 1/26/13 Link.com/east/2es 13 3/9/13 Link.com/east/2es 3 9/29/12 Link.com/east/2es 2 12/8/12 Link.com/east/2es 4 3/2/13 Link.com/east/2es 8 2/23/13 Link.com/east/2es 17 12/29/12 Link.com/east/2es 78 4/13/13 Link.com/east/2es 2 12/1/12 Link.com/east/2es 1 2/2/13 Link.com/east/2es 13 3/30/13 Link.com/east/2es 1 5/18/13 Link.com/east/2es 2 12/22/12 Link.com/east/2es 64 1/5/13 Link.com/east/2es 64 6/1/13 Link.com/east/2es 9 2/9/13 Link.com/east/2es 6 6/8/13 Link.com/east/2es 5 4/27/13 Link.com/east/2es 1 7/13/13 Link.com/east/2es 2 2/16/13 Link.com/east/2es 2 3/16/13 Link.com/east/2es 3 5/18/13 Link.com/east/2es 3 6/1/13 Link.com/east/2es 2 9/28/13 Link.com/east/2es 1 4/20/13 Link.com/east/2es 8 5/4/13 Link.com/east/2es 4 3/23/13 Link.com/east/2es 6 8/31/13 Link.com/east/2es 1 11/2/13 Link.com/east/2es 2 5/25/13 Link.com/east/2es 38 10/5/13 Link.com/east/2es 2 6/22/13 Link.com/east/2es 9 8/10/13 Link.com/east/2es 1 9/14/13 Link.com/east/2es 3 6/29/13 Link.com/east/2es 3 12/14/13 Link.com/east/2es 3 1/18/14 Link.com/east/2es 4 12/7/13 Link.com/east/2es 3 8/3/13 Link.com/east/2es 4 1/11/14 Link.com/east/2es 4 9/21/13 Link.com/east/2es 4 10/12/13 Link.com/east/2es 1 2/1/14 Link.com/east/2es 4 7/21/12 Link.com/east/2es 20 11/17/12 Link.com/east/2es 4 12/8/12 Link.com/east/2es 348 1/12/13 Link.com/east/2es 35 4/6/13 Link.com/east/2es 2 7/20/13 Link.com/east/2es 3 8/24/13 Link.com/east/2es 1 9/7/13 Link.com/east/2es 1 1/25/14 Link.com/east/2es 1 ; ... View more

Re: Proc Transpose Issue

by Obsidian | Level 7 in SAS Programming
‎11-27-2012 03:58 PM
‎11-27-2012 03:58 PM
Thanks Art. Last thing. There aren't necessarily 7 responses in each data set  There could be fore or there could be 100 it changes every time.  ... View more

Re: Proc Transpose Issue

by Obsidian | Level 7 in SAS Programming
‎11-27-2012 02:36 PM
‎11-27-2012 02:36 PM
Hi Arthur-- Sorry. I should have specified what. I am interested in all of the Question values, I just didn't add G5 and G6  to the sample table. There are actually over 100 questions. Thanks-- ... View more

Proc Transpose Issue

by Obsidian | Level 7 in SAS Programming
‎11-27-2012 02:21 PM
‎11-27-2012 02:21 PM
Hi all-- I'm having trouble with this transpose. When I try to transpose the data set below I get an error message that says duplicate variables in the by group so I need to rename the variables. Id Question responce1 responce2 responce3 responce4 responce5 responce6 responce7 fgg45 S1 YES fgg45 S2 NO NO NO NO NO NO fgg45 G5 YES YES YES YES YES YES YES fgg45 G6 NO gg453 S1 YES YES gg453 S2 NO NO NO NO NO NO NO gg453 G5 YES YES YES gg453 G6 NO NO NO NO What I need should look like this. I need to add a _#x to the end of each question depending on how many responses there are. ID S1_1x S1_2x S2_1x S2_2x S2_3x S2_4x S2_5x S2_6x S2_7x fgg45 YES NO NO NO NO NO NO gg453 YES YES NO NO NO NO NO NO NO Any assistance is greatly appreciated. Matt-- ... View more

Add “N/A” to a Percent Format

by Obsidian | Level 7 in ODS and Base Reporting
‎11-12-2012 03:34 PM
‎11-12-2012 03:34 PM
Hi all— I have been using the format percent6 but I need to alter it a little so all missing values are “N/A”. Is there a way to write a format that will do that? Thanks! ... View more

Defining a Password by Characters in File Name

by Obsidian | Level 7 in SAS Programming
‎10-04-2012 03:52 PM
‎10-04-2012 03:52 PM
Hi all-- This may be a tricky question. I use a Macro which password protects xml files. The macro %includeds a .vbs program. options noxsync noxwait; %include "X:\Programs\convert.sas"; %convert_files(default=X:\Datasets\Scores,ext=xml,password=test); When the program runs it password protects every file in the folder with the same password -- in the case above "test. I need to change this so the password is different for every file. The password starts with the letters ORE and then the code which is the first three characters in the file name.   For file : "700 FY13  Report.xml" The password needs to be ORE700 For file: "B06 FY13  Report.xml" The password needs to be OREB06 For file: "J10 FY13 Q1  Report.xml" The password needs to be OREJ10 Is there a way to read the first three characters of a file name and then use those characters in the password? You feedback is appreciated! ... View more

Re: scoring based on a range of values

by Obsidian | Level 7 in SAS Procedures
‎09-12-2012 07:37 AM
‎09-12-2012 07:37 AM
Hi Ksharp-- Thank you for your help. adding a percent does not seem to be working because im trying to modify a format  not change the format.  I've been trying to achive this by using a picture format but I can't seem to get it right. This is what I have: proc format; picture  Utl      .97-1='100%'      .93-.96='70%'      .90-.92='50%      low-.89='0%'; run; Your feedback is appreciated. ... View more

Re: scoring based on a range of values

by Obsidian | Level 7 in SAS Procedures
‎09-11-2012 11:53 AM
‎09-11-2012 11:53 AM
Thanks Ksharp. Quick question: is there a way to add an additional format to this. I need to have the scores in percents. so 100%, 7%, 5%, 0% proc format; value Utl      .97-1=1      .93-.96=.7      .90-.92=.5      low-.89=0; run; ... View more

scoring based on a range of values

by Obsidian | Level 7 in SAS Procedures
‎09-10-2012 01:13 PM
‎09-10-2012 01:13 PM
Hi all— I have to assign a score based on a range of values. I don’t think I’m doing this the best way. For some of the cases a missing value is returned.  I have attached a sample data set.  Also, how would this translate into base SAS? Any help is greatly appreciated. Matt-- proc sql; create table want as select distinct AGENCY_NAME, type_new, Eight_Months_Total_sum, Number_Active, percent_18, case when (Type_NEW='GP') and  0 le percent_18 le .12   then 1      when (Type_NEW='GP') and .13 le percent_18 le .17  then .8      when (Type_NEW='GP') and .18 le percent_18 le .24  then .6   when (Type_NEW='GP') and .25 le  percent_18 le .41  then .4      when (Type_NEW='GP') and    percent_18 gt .40 then 0   when (Type_NEW='FTR') and  0 le percent_18 le .11   then  1      when (Type_NEW='FTR') and .12 le percent_18 le .14  then .8      when (Type_NEW='FTR') and .15 le percent_18 le .22  then .6   when (Type_NEW='FTR') and .23 le  percent_18 le .34  then .4      when (Type_NEW='FTR') and  percent_18 gt .4 then 0   end as LOS_Score  format=percent6.    from have group by AGENCY_NAME,Type_NEW  ; quit; ... View more

Re: Shorting variable length

by Obsidian | Level 7 in SAS Procedures
‎08-30-2012 10:23 AM
‎08-30-2012 10:23 AM
Thanks Hai. kuo. Its not exatlly wat I am looking for though. I'm looking to shorten the actually variable names not the cell contents. Do you know how to do that? ... View more

Re: Shorting variable length

by Obsidian | Level 7 in SAS Procedures
‎08-30-2012 09:27 AM
‎08-30-2012 09:27 AM
Cut off at X charters. Thanks!  ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎05-13-2019 07:07 PM
Likes given to
View All
Likes from
View All
Quick links