About Singham20

Singham20 · ‎01-24-2020

Hi! I am having trouble with applying risk adjustments to my various projects. What I have is 15 Projects that start in the beginning of quarters over the next 3 years. These projects all have a potential earning associated with them. The potential earnings do not change for a project once it starts. For instance Project 1 starts on 1/1/2020 and has a potential earning of 2. This means starting 1/1/2020 and through the end of the timeframe in the data table the earning of Project 1 will be 2 for every month. So for Project 7, which starts on 7/1/2020 and has a potential earning of 1, it will have no earnings from 1/1/2020 to 6/1/2020; however on 7/1/2020 and onwards it will have a potential earning of 1. From here is where the confusion arises for me. We expect that there is a good chance that the potential earnings will not be met so we need to account for that. Regardless of the start date the risk adjustment is as follows: For first 3 months we will only see 30%. For the next 3 months we will see 60%. For 6+ months since start date we should see 80% of the potential. 1-3 months from start date: (30% * Potential Earnings) 4-6 months from start date: (60% * Potential Earnings) 7+ months from start date: (80% * Potential Earnings) So for Project 1 which as an earnings potential of 2 and starts in 1/1/2020 I should see the following: 1/1/2020 (2*30%) 2/1/2020 (2*30%) 3/1/2020 (2*30%) 4/1/2020 (2*60%) 5/1/2020 (2*60%) 6/1/2020 (2*60%) 7/1/2020 (2*80%) 9/1/2020 (2*80%) And so on … After we get these risk adjustments to all the projects depending on the start data I need to sum it for each month of the year and group the total by the Group variable, which is A or B. Below is a table to see what I am talking about. Hope someone can help me on this SAS Homework! Group A or B A B A A B A B B B B B A A A B Project Number Project 1 Project 2 Project 3 Project 4 Project 5 Project 6 Project 7 Project 8 Project 9 Project 10 Project 11 Project 12 Project 13 Project 14 Project 15 Project Start Date 1/1/2020 1/1/2020 1/1/2020 1/1/2020 1/1/2020 7/1/2020 7/1/2020 7/1/2020 10/1/2020 10/1/2020 4/1/2021 4/1/2021 1/1/2022 1/1/2022 10/1/2022 Expected Earnings 2 4 2 1 3 22 1 8 4 2 5 1 2 3 4 Date Quarter 1/1/2020 Q1 2 4 2 1 3 2/1/2020 Q1 2 4 2 1 3 3/1/2020 Q1 2 4 2 1 3 4/1/2020 Q2 2 4 2 1 3 5/1/2020 Q2 2 4 2 1 3 6/1/2020 Q2 2 4 2 1 3 7/1/2020 Q3 2 4 2 1 3 22 1 8 8/1/2020 Q3 2 4 2 1 3 22 1 8 9/1/2020 Q3 2 4 2 1 3 22 1 8 10/1/2020 Q4 2 4 2 1 3 22 1 8 4 2 11/1/2020 Q4 2 4 2 1 3 22 1 8 4 2 12/1/2020 Q4 2 4 2 1 3 22 1 8 4 2 1/1/2021 Q1 2 4 2 1 3 22 1 8 4 2 2/1/2021 Q1 2 4 2 1 3 22 1 8 4 2 3/1/2021 Q1 2 4 2 1 3 22 1 8 4 2 4/1/2021 Q2 2 4 2 1 3 22 1 8 4 2 5 1 5/1/2021 Q2 2 4 2 1 3 22 1 8 4 2 5 1 6/1/2021 Q2 2 4 2 1 3 22 1 8 4 2 5 1 7/1/2021 Q3 2 4 2 1 3 22 1 8 4 2 5 1 8/1/2021 Q3 2 4 2 1 3 22 1 8 4 2 5 1 9/1/2021 Q3 2 4 2 1 3 22 1 8 4 2 5 1 10/1/2021 Q4 2 4 2 1 3 22 1 8 4 2 5 1 11/1/2021 Q4 2 4 2 1 3 22 1 8 4 2 5 1 12/1/2021 Q4 2 4 2 1 3 22 1 8 4 2 5 1 1/1/2022 Q1 2 4 2 1 3 22 1 8 4 2 5 1 2 3 2/1/2022 Q1 2 4 2 1 3 22 1 8 4 2 5 1 2 3 3/1/2022 Q1 2 4 2 1 3 22 1 8 4 2 5 1 2 3 4/1/2022 Q2 2 4 2 1 3 22 1 8 4 2 5 1 2 3 5/1/2022 Q2 2 4 2 1 3 22 1 8 4 2 5 1 2 3 6/1/2022 Q2 2 4 2 1 3 22 1 8 4 2 5 1 2 3 7/1/2022 Q3 2 4 2 1 3 22 1 8 4 2 5 1 2 3 8/1/2022 Q3 2 4 2 1 3 22 1 8 4 2 5 1 2 3 9/1/2022 Q3 2 4 2 1 3 22 1 8 4 2 5 1 2 3 10/1/2022 Q4 2 4 2 1 3 22 1 8 4 2 5 1 2 3 4 11/1/2022 Q4 2 4 2 1 3 22 1 8 4 2 5 1 2 3 4 12/1/2022 Q4 2 4 2 1 3 22 1 8 4 2 5 1 2 3 4 1/1/2023 Q1 2 4 2 1 3 22 1 8 4 2 5 1 2 3 4 2/1/2023 Q1 2 4 2 1 3 22 1 8 4 2 5 1 2 3 4 3/1/2023 Q1 2 4 2 1 3 22 1 8 4 2 5 1 2 3 4 4/1/2023 Q2 2 4 2 1 3 22 1 8 4 2 5 1 2 3 4 5/1/2023 Q2 2 4 2 1 3 22 1 8 4 2 5 1 2 3 4 6/1/2023 Q2 2 4 2 1 3 22 1 8 4 2 5 1 2 3 4 7/1/2023 Q3 2 4 2 1 3 22 1 8 4 2 5 1 2 3 4 8/1/2023 Q3 2 4 2 1 3 22 1 8 4 2 5 1 2 3 4 9/1/2023 Q3 2 4 2 1 3 22 1 8 4 2 5 1 2 3 4 10/1/2023 Q4 2 4 2 1 3 22 1 8 4 2 5 1 2 3 4 11/1/2023 Q4 2 4 2 1 3 22 1 8 4 2 5 1 2 3 4 12/1/2023 Q4 2 4 2 1 3 22 1 8 4 2 5 1 2 3 4

Singham20 · ‎06-07-2019

So here is how the variable look. I am just showing 5, however there are 24,785 variables. Time _48164_1 _W468s_1 _di4ek452_5 _47816_1 _zidle_45 1/1/2017 0:00 6.818272 4.030038 8.213272 0.060326 9.521269 1/1/2017 1:00 0.100979 3.009709 2.359012 1.749067 8.0581 1/1/2017 2:00 1.20293 0.524906 0.686842 2.561308 3.58268 1/1/2017 3:00 0.178333 8.815986 7.657224 5.581614 1.142414 1/1/2017 4:00 6.654463 2.398171 2.696395 2.181824 2.000096 1/1/2017 5:00 5.428405 7.665879 7.996566 9.112351 3.012801 1/1/2017 6:00 7.710257 1.296825 7.965649 1.278932 8.198926 1/1/2017 7:00 5.733929 8.763634 7.37632 4.684965 6.252313 1/1/2017 8:00 0.173659 8.191108 7.592309 0.764799 1.81264 1/1/2017 9:00 2.782826 3.713065 8.526002 1.06912 9.897541 1/1/2017 10:00 4.46292 4.154905 4.766856 2.918054 1.398898 1/1/2017 11:00 1.949195 1.306601 4.989425 6.146979 6.172791 1/1/2017 12:00 9.865355 5.047937 2.965091 8.109938 0.024781 1/1/2017 13:00 6.361114 0.850972 3.595049 1.18486 3.629694 1/1/2017 14:00 7.494559 7.221369 9.410831 1.467266 2.488785 1/1/2017 15:00 2.129789 5.699575 0.627782 5.550582 0.275467 1/1/2017 16:00 9.046102 5.785753 0.764385 5.695247 6.812834 1/1/2017 17:00 8.056089 2.70044 0.916423 8.568327 3.729957 1/1/2017 18:00 9.318779 7.345054 5.430452 0.23345 0.561244 1/1/2017 19:00 4.827045 9.602832 1.841213 9.62645 8.05601 1/1/2017 20:00 1.623731 9.719941 4.036305 2.812807 7.412819 1/1/2017 21:00 2.199275 7.198554 0.28249 0.405406 6.038437 1/1/2017 22:00 8.08961 6.457083 8.185543 7.05954 5.085864 1/1/2017 23:00 3.040053 7.585161 8.839786 2.42752 7.113683 1/1/2017 23:00 3.040053 7.585161 8.839786 2.42752 7.113683

Singham20 · ‎06-06-2019

So I struggled and finally got the solution: Time=input(time2,anydtdtm.) format Time datetime16.;

Singham20 · ‎06-06-2019

Thank you for all your help! Quick question. The dataset I showed only had 5 observations. My actual dataset has thousands each with unique names that is not as simple as T1, T2, and so on. What can I change the array to so it handles this?

Singham20 · ‎06-06-2019

I did look at that before posting the paper. However the formats do not seem to convert the character string to SAS time. It just comes up as a blank.

Singham20 · ‎06-06-2019

Hi all! I am struggling to convert a character string into a time variable. I have a dataset that imports the Time variable like: 11/11/2011 11:20 SAS turns this into a Character in the $16. Format. However, I need the Time to display in the DATETIME16. Format. How do I go about doing this? I have tried an input statement already. Here is the dataset as I see it in Excel: Name Time A 11/15/2012 09:16 B 11/15/2012 09:01 C 11/07/2012 09:51 D 11/28/2012 12:16 E 12/13/2012 01:31 F 12/10/2012 09:36 Thank you!

Singham20 · ‎06-06-2019

Hi SAS Community! I was hoping someone could help me with the following issue. I have 2 data sets, Dataset 1 and Dataset 2. I need to use Dataset 2 to manipulate the data in Dataset 1 to create Dataset 3. Dataset 2 has the Start time of the data and I need all the data that is populated before the Start time for each variable to be changed to blank. Below is the visuals for this process. Dataset 1 Time T1 T2 T3 T4 T5 1/1/2017 0:00 6.818272 4.030038 8.213272 0.060326 9.521269 1/1/2017 1:00 0.100979 3.009709 2.359012 1.749067 8.0581 1/1/2017 2:00 1.20293 0.524906 0.686842 2.561308 3.58268 1/1/2017 3:00 0.178333 8.815986 7.657224 5.581614 1.142414 1/1/2017 4:00 6.654463 2.398171 2.696395 2.181824 2.000096 1/1/2017 5:00 5.428405 7.665879 7.996566 9.112351 3.012801 1/1/2017 6:00 7.710257 1.296825 7.965649 1.278932 8.198926 1/1/2017 7:00 5.733929 8.763634 7.37632 4.684965 6.252313 1/1/2017 8:00 0.173659 8.191108 7.592309 0.764799 1.81264 1/1/2017 9:00 2.782826 3.713065 8.526002 1.06912 9.897541 1/1/2017 10:00 4.46292 4.154905 4.766856 2.918054 1.398898 1/1/2017 11:00 1.949195 1.306601 4.989425 6.146979 6.172791 1/1/2017 12:00 9.865355 5.047937 2.965091 8.109938 0.024781 1/1/2017 13:00 6.361114 0.850972 3.595049 1.18486 3.629694 1/1/2017 14:00 7.494559 7.221369 9.410831 1.467266 2.488785 1/1/2017 15:00 2.129789 5.699575 0.627782 5.550582 0.275467 1/1/2017 16:00 9.046102 5.785753 0.764385 5.695247 6.812834 1/1/2017 17:00 8.056089 2.70044 0.916423 8.568327 3.729957 1/1/2017 18:00 9.318779 7.345054 5.430452 0.23345 0.561244 1/1/2017 19:00 4.827045 9.602832 1.841213 9.62645 8.05601 1/1/2017 20:00 1.623731 9.719941 4.036305 2.812807 7.412819 1/1/2017 21:00 2.199275 7.198554 0.28249 0.405406 6.038437 1/1/2017 22:00 8.08961 6.457083 8.185543 7.05954 5.085864 1/1/2017 23:00 3.040053 7.585161 8.839786 2.42752 7.113683 Dataset 2 Name Start T1 1/1/2017 7:00 T2 1/1/2017 2:00 T3 1/1/2017 15:00 T4 1/1/2017 0:00 T5 1/1/2017 20:00 This is the desired output: Final Dataset Time T1 T2 T3 T4 T5 1/1/2017 0:00 0.060326 1/1/2017 1:00 1.749067 1/1/2017 2:00 0.524906 2.561308 1/1/2017 3:00 8.815986 5.581614 1/1/2017 4:00 2.398171 2.181824 1/1/2017 5:00 7.665879 9.112351 1/1/2017 6:00 1.296825 1.278932 1/1/2017 7:00 5.733929 8.763634 4.684965 1/1/2017 8:00 0.173659 8.191108 0.764799 1/1/2017 9:00 2.782826 3.713065 1.06912 1/1/2017 10:00 4.46292 4.154905 2.918054 1/1/2017 11:00 1.949195 1.306601 6.146979 1/1/2017 12:00 9.865355 5.047937 8.109938 1/1/2017 13:00 6.361114 0.850972 1.18486 1/1/2017 14:00 7.494559 7.221369 1.467266 1/1/2017 15:00 2.129789 5.699575 0.627782 5.550582 1/1/2017 16:00 9.046102 5.785753 0.764385 5.695247 1/1/2017 17:00 8.056089 2.70044 0.916423 8.568327 1/1/2017 18:00 9.318779 7.345054 5.430452 0.23345 1/1/2017 19:00 4.827045 9.602832 1.841213 9.62645 1/1/2017 20:00 1.623731 9.719941 4.036305 2.812807 7.412819 1/1/2017 21:00 2.199275 7.198554 0.28249 0.405406 6.038437 1/1/2017 22:00 8.08961 6.457083 8.185543 7.05954 5.085864 1/1/2017 23:00 3.040053 7.585161 8.839786 2.42752 7.113683 Hopefully someone out there can help on this! Thank you!

Singham20 · ‎04-30-2019

Thank you so much! This does work! I know how to get the order for the months if it was in numeric form however how would I order the months in there current form? Thank you!

Singham20 · ‎04-30-2019

Hi all! I am having trouble transposing my data. Here is what I have: Name Month Value City Jon Jan 11 Orlando Jon Feb 42 Orlando Jon Mar 34 Orlando Jon Apr 82 Orlando Jill Jan 24 Miami Jill Feb 4 Miami Jill Mar 45 Miami Cam Jan 98 Houston Cam Feb 42 Houston Cam Mar 48 Houston Cam Apr 11 Houston Terry Feb 68 Austin Terry Apr 72 Houston Here is what I need: Name Jan Feb Mar Apr City Jon 11 42 34 82 Orlando Jill 24 4 45 Miami Cam 98 42 48 11 Houston Terry 68 72 Error I know proc transpose can be used to transpose however I do not know how to achieve my end table. Notice I put "Error" as the City for Terry; this is because Terry has more than one city for him and I want that to be flagged. Does anyone have any thoughts on this? Thank you so much for all your help!

Singham20 · ‎05-15-2018

Thank you so much! This worked for Hours 0 to 21. I will add a step to take into account for the last 3 hours.

Singham20 · ‎05-14-2018

Hi all, I have an excel sheet with weather data for the full year (2017) in 3 hour increments as shown below: TEMP-MAX TEMP-MIN TEMP0 TEMP3 TEMP6 TEMP9 TEMP12 TEMP15 TEMP18 TEMP21 Date 32.00 23.00 27.00 25.00 23.00 26.10 32.00 30.00 28.00 28.00 1/1/2017 32.00 28.00 28.00 28.00 28.90 30.00 30.90 32.00 32.00 32.00 1/2/2017 32.00 3.00 32.00 32.00 25.00 14.00 12.00 10.90 6.10 3.00 1/3/2017 3.00 -5.10 1.00 -2.00 -5.10 -4.00 1.90 3.00 1.00 0.00 1/4/2017 1.00 -6.00 0.00 -2.90 -6.00 -2.90 1.00 1.00 -2.00 -6.00 1/5/2017 5.00 -9.00 -9.00 -9.00 -9.00 -6.00 1.90 5.00 3.90 1.90 1/6/2017 8.10 0.00 0.00 0.00 1.00 3.00 8.10 8.10 3.90 1.00 1/7/2017 16.00 -4.00 -0.90 -2.90 -4.00 1.00 10.90 12.90 14.00 16.00 1/8/2017 26.10 18.00 18.00 19.00 18.00 19.00 21.00 21.90 24.10 26.10 1/9/2017 27.00 5.00 27.00 26.10 27.00 27.00 21.90 12.90 8.10 5.00 1/10/2017 9.00 5.00 5.00 6.10 6.10 6.10 9.00 8.10 6.10 6.10 1/11/2017 I need to create a SAS dataset that will interpolate this 3 hour weather data into hourly data as shown below: *temp is made up to give example hourend Temp 01JAN2017:01:00:00 24.55 01JAN2017:02:00:00 25.3 01JAN2017:03:00:00 26.05 01JAN2017:04:00:00 26.8 01JAN2017:05:00:00 27.4833 01JAN2017:06:00:00 28.1667 01JAN2017:07:00:00 28.85 01JAN2017:08:00:00 29.85 01JAN2017:09:00:00 30.85 01JAN2017:10:00:00 31.85 01JAN2017:11:00:00 33.65 01JAN2017:12:00:00 35.45 01JAN2017:13:00:00 37.25 01JAN2017:14:00:00 37.5 01JAN2017:15:00:00 37.75 01JAN2017:16:00:00 38 01JAN2017:17:00:00 37 01JAN2017:18:00:00 36 01JAN2017:19:00:00 35 01JAN2017:20:00:00 32.8666 01JAN2017:21:00:00 30.7333 01JAN2017:22:00:00 28.6 01JAN2017:23:00:00 27.25 02JAN2017:00:00:00 25.9 02JAN2017:01:00:00 23.55 02JAN2017:02:00:00 22.9333 02JAN2017:03:00:00 22.3167 02JAN2017:04:00:00 21.7 02JAN2017:05:00:00 21.5167 02JAN2017:06:00:00 21.3333 02JAN2017:07:00:00 21.15 02JAN2017:08:00:00 22.3833 02JAN2017:09:00:00 23.6167 02JAN2017:10:00:00 24.85 02JAN2017:11:00:00 27.1 02JAN2017:12:00:00 29.35 Thank you very much for all you help.

Singham20 · ‎10-23-2017

This would work great if I had just one survey to do however, I have a lot of surveys to do and many of them have different number of strata. I will need to write a proc sql I think.

Singham20 · ‎10-23-2017

Hi- I am struggling stratifying my sample that has a predefined bound in SAS. For example: Say I have a dataset named "PopSamp" with many sample points and different prices like below: Sample Points Price A .87 B 1.97 C .27 D 2.27 E 4.97 F .17 G 2.42 H .03 I 5.99 J 3.69 Now suppose I have a dataset named "StrataBounds" with my strata upper bounds as follows: Strata Upper Bound 1 0.42 2 1.50 3 3.99 4 999999 May someone help me figure out how I can write a code that will use my "PopSamp" dataset and add a variable strata using the "StrataBounds" dataset? This would be done by seeing in what strata the price is found. My final dataset should look like this: Sample Points Price Strata A .87 2 B 1.97 3 C .27 1 D 2.27 3 E 4.97 4 F .17 1 G 2.42 3 H .03 1 I 5.99 4 J 3.69 3 Thank you!

Singham20 · ‎09-11-2017

Thank you so much! I cannot believe I overlooked that.

Singham20 · ‎09-11-2017

Hi everyone! I am trying to copy and rename a bunch of files on my computer and thought X commands is the way to go. I tried it and it worked perfectly however when I put in a Macro to do this it does not work. it just saves the first file with the name stated and does not replace the "&name" with name given. Here is what I have written so far: %FAST(2017,Carry,05T); 　 %macro FAST(YEAR,SURVEY,MO); X "copy ""C:\TestSASCode\&YEAR\&SURVEY\&MO\test.DAT"" ""C:\TestSASCode\SAS\&SURVEY_&YEAR_&MO_2017-carrier.DAT"" "; %mend; %FAST(2017,Carry,01T); %FAST(2017,Carry,02T); %FAST(2017,Carry,03T); I know it looks like I do not need the &Year and &survey because they are the same however I will be doing this for different year and surveys too. I just wanted the question to be simple. What do you guys think I should do? Thank you!

Online Status	Offline
Date Last Visited	‎01-29-2020 09:07 PM

Using SAS to add Risk Adjustments based on Start Date to Earnings

Re: Data Manipulation with Base SAS

Re: How to convert Character string to SAS Datetime16. in Base SAS

Re: Data Manipulation with Base SAS

Re: How to convert Character string to SAS Datetime16. in Base SAS

How to convert Character string to SAS Datetime16. in Base SAS

Data Manipulation with Base SAS

Re: Transpose Data with groups

Transpose Data with groups

Re: How to interpolate weather data

Using SAS to add Risk Adjustments based on Start Date to Earnings

Re: Data Manipulation with Base SAS

Re: How to convert Character string to SAS Datetime16. in Base SAS

Re: Data Manipulation with Base SAS

Re: How to convert Character string to SAS Datetime16. in Base SAS

How to convert Character string to SAS Datetime16. in Base SAS

Data Manipulation with Base SAS

Re: Transpose Data with groups

Transpose Data with groups

Re: How to interpolate weather data

How to interpolate weather data

Re: Stratify a sample using predefined bounds

Stratify a sample using predefined bounds

Re: Executing a X Command with macro

Executing a X Command with macro