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: 
AshPatel
‎12-10-2021
  • 9 Posts
  • 2 Likes Given
  • 0 Solutions
  • 0 Likes Received

SQL: match record with previous year on same business day

by Fluorite | Level 6 in New SAS User
‎12-05-2021 03:34 PM
‎12-05-2021 03:34 PM
I have a data table as below I would like to match the same business day (same month), current year vs. previous.  I have no idea where to start.  I can hard code the comparable business day date of last year but there must be a way to pick the same business day date for Sept 2020 For example, September 2021 and September 2020 20210901 (business day 1) match with 20200901 20210906 (business day 5) match with 20200905 20210907 (business day 6) match with 20200906 here is the partial table and output below Date Year BusinessDay NoofItems Volume 20210901 2021 1 918 980221 20210902 2021 2 775 760314 20210903 2021 3 659 701345 20210904 2021 4 1012 0 20210906 2021 5 883 0 20210907 2021 6 899 0 20210908 2021 7 916 0 20210909 2021 8 932 0 20200901 2020 1 1082 932456 20200902 2020 2 1098 914315 20200903 2020 3 1115 962419 20200904 2020 4 1132 1062458 20200905 2020 5 1148 1123489 20200907 2020 6 1165 1326178 20200908 2020 7 1181 1464218   Desired output as below Date Business_Day MTD_Volume_Sep2021 MTD_Volume_Sep2020 YoY_Changed 20210901 1 980221 932456 5% 20210902 2 1740535 1846771 -6% 20210903 3 2441880 2809190 -13% 20210904 4 2441880 3871648 -37% 20210905 5 2441880 4995137 -51% 20210906 6 2441880 6321315 -61% 20210907 7 2441880 7785533 -69% ... View more

Re: Shortest Distance

by Fluorite | Level 6 in SAS Programming
‎03-09-2021 06:59 AM
‎03-09-2021 06:59 AM
Thanks Phil. These are fake data. Any help would be greatly appreciated. ... View more

Re: Shortest Distance

by Fluorite | Level 6 in SAS Programming
‎03-05-2021 05:43 PM
‎03-05-2021 05:43 PM
Hi Phil, I have reloaded and pasted have1 and have2 again..Please let me know if stil issue with the data ... View more

Re: Shortest Distance_var2

by Fluorite | Level 6 in SAS Programming
‎03-05-2021 03:03 PM
‎03-05-2021 03:03 PM
have1 ZIP ClientNo City State 14201 A95572 Cedar Grove TA 14204 A91374 Cedar Grove NH 14206 A71649 Bristol KA 14207 A63397 Buffalo MI 14208 A06957 Cedar Grove NJ 14209 A58062 Cedar Grove ID 14210 A43865 Bristol IL 14211 A06391 Bristol HA 14212 A54498 Cedar Grove NC 14213 A09533 Buffalo UT 14213 A09534 Buffalo UT 14214 A31165 Buffalo DC 14215 A52498 Buffalo NY 14216 A77384 Buffalo CL 14220 A73552 Buffalo OH 14220 A73553 Buffalo OH 14220 A73554 Buffalo OH 14222 A66949 Bristol SC 14222 A66950 Bristol SC 14222 A66951 Bristol SC 14222 A66952 Bristol SC 14222 A66953 Bristol SC 14222 A66954 Bristol SC 14222 A66955 Bristol SC   have2 ZIP Miles_Rank Distance_Miles City State 14201 1 1.1 Cedar Grove TA 14201 2 5.3 Sloan NY 14201 3 9.3 Amherst CL 14201 4 15.5 Buffalo MI 14201 5 30.1 Mo Town NJ 14204 1 0.3 Cedar Grove HA 14204 1 0.3 Blasdell CL 14204 2 1.5 Cedar Grove HA 14204 4 12 Blasdell TA 14204 5 15 Cedar Grove HA 14211 1 1.1 Buffalo MI 14211 2 5.3 Mo Town NJ 14211 3 9.3 Bristol HA 14211 4 15.5 Amherst CL 14211 5 30.1 Cedar Grove HA 14213 1 0.4 Buffalo MI 14213 2 1.5 Buffalo UT 14213 3 8 Blasdell CL 14213 4 12 Buffalo NJ 14213 5 15 Blasdell TA 14214 1 0.2 Buffalo DC 14214 2 0.4 Buffalo DC 14214 3 1.5 Buffalo DC 14214 4 8 Buffalo DC 14214 5 15 Buffalo DC 14215 1 1.2 Sloan CO 14215 2 2 Amherst AL 14215 3 2.5 Buffalo NJ 14215 4 6 Buffalo NY 14215 5 10 Kenmore DE 14216 1 2 Cheektowaga AR 14216 2 7.2 Great Hill GE 14216 3 8.8 Buffalo NJ 14216 4 14.2 Fort Erie XX 14216 5 21.6 Buffalo CL 14220 1 3 Buffalo WY 14220 2 4.6 Milton NE 14220 3 8 Milton WA 14220 4 14.9 Buffalo OH 14220 5 26.2 Milton TE 14222 1 3 Bristol SC 14222 2 4.6 Bristol SC 14222 3 8 Bristol SC ... View more

Shortest Distance

by Fluorite | Level 6 in SAS Programming
‎03-05-2021 07:59 AM
‎03-05-2021 07:59 AM
The solution (Shortest Distance Match) that was provided earlier this week worked fine, however; I have been asked to modify the criteria as below.  I tried modifying the data step but unable to get the expected output.  I can take dividual Miles_Rank one at a time in a separate data step but in our actual data file, we have 10 unique Miles_Ranks (1 to10) for each ZIP.  I believe, there must be a way to have the Miles Ranks logic build in one or two data steps.  I am attaching revised raw data (have1 and have2) and also included expected output sheet.  Sorry, but I am posting this again.  Once again thank you so much!   1. Match on zip, city and state (have1 and have2) 2. Want1 all records from have1 with ClientNo where there is a match based on the shortest distance (Miles_Rank) 3. Also, Want2 a list of records from have1 where there is no match ... View more

Re: Shortest distance match.

by Fluorite | Level 6 in SAS Programming
‎03-04-2021 03:10 PM
‎03-04-2021 03:10 PM
The solution that was provided earlier this week worked fine, however; I have been asked to modify the criteria as below.  I tried modifying the data step but unable to get the expected output.  I can take dividual Miles_Rank one at a time in a separate data step but in our actual data file, we have 10 unique Miles_Ranks (1 to10) for each ZIP.  I believe, there must be a way to have the Miles Ranks logic build in one or two data steps.  I am attaching revised raw data (have1 and have2) and also included expected output sheet.  Once again thank you so much!   1. Match on zip, city and state (have1 and have2) 2. Want1 all records from have1 with ClientNo where there is a match based on the shortest distance (Miles_Rank) 3. Also, Want2 a list of records from have1 where there is no match ... View more

Re: Shortest distance match.

by Fluorite | Level 6 in SAS Programming
‎03-01-2021 08:43 AM
‎03-01-2021 08:43 AM
Yes, the solution worked perfactly.  Thank you ever so much. ... View more

Re: Shortest distance match.

by Fluorite | Level 6 in SAS Programming
‎02-28-2021 03:10 PM
‎02-28-2021 03:10 PM
sorry, I should have said match based on ZIP and City.  I would like to have the match based on the shortest distance (e.g. in case of zip 14213, the output would be SEQ=1 and not SEQ=3.  If there is no match for a record in Dataset_1, then need to have a seprate output for it (e.g. ZIP 14208) ... View more

Shortest distance match.

by Fluorite | Level 6 in SAS Programming
‎02-28-2021 10:12 AM
‎02-28-2021 10:12 AM
Hello there, Can someone help me with the following.  I have the following two datasets.  Ned to join Dataset_1 with Dataset_2 on ZIP and City.  Need to match only once based on the shortest Distance_mile. If match, then create a dataset (e.g. matched), If do not match, then create a dataset (e.g. Notmatched).  Again, if Clientno match twice, I need to pick only the one with the shortest distance.  Sorry, the Dataset_2 is huge, so only sample fake data here   Dataset_1       Dataset_2     ZIP ClientNo City   ZIP SEQ Distance_Miles City 14215 A52498 Buffalo   14215 1 1.2 Sloan 14213 A09533 Buffalo   14215 2 2.0 Amherst 14207 A63397 Buffalo   14215 3 2.5 Buffalo 14216 A77384 Buffalo   14215 4 6.0 Seneca 14220 A73552 Buffalo   14215 5 10.0 Kenmore 14214 A31165 Buffalo   14213 1 0.4 Buffalo 14211 A06391 Bristol   14213 2 1.5 Kenmore 14206 A71649 Bristol   14213 3 8.0 Blasdell 14222 A66949 Bristol   14213 4 12.0 Buffalo 14210 A43865 Bristol   14213 5 15.0 Blasdell 14201 A95572 Cedar Grove   14201 1 1.1 Cedar Grove 14208 A06957 Cedar Grove   14201 2 5.3 Sloan 14204 A91374 Cedar Grove   14201 3 9.3 Amherst 14209 A58062 Cedar Grove   14201 4 15.5 Buffalo 14212 A54498 Cedar Grove   14201 5 30.1 Mo Town         14216 1 2.0 Cheektowaga         14216 2 7.2 Great Hill         14216 3 8.8 Buffalo         14216 4 14.2 Fort Erie         14216 5 21.6 Ajax         14220 1 3.0 Sloan         14220 2 4.6 Amherst         14220 3 8.0 Cheektowaga         14220 4 14.9 Great Hill         14220 5 26.2 Milton ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎12-10-2021 11:47 AM
Likes given to
View All
Quick links