09-01-2015
Red_Squirrel
Calcite | Level 5
Member since
05-20-2015
- 15 Posts
- 1 Likes Given
- 0 Solutions
- 0 Likes Received
-
Latest posts by Red_Squirrel
Subject Views Posted 1479 06-12-2015 11:34 AM 1479 06-11-2015 11:48 AM 3140 06-11-2015 11:06 AM 1558 06-11-2015 11:02 AM 3279 06-11-2015 10:59 AM 544 05-21-2015 10:08 AM 544 05-21-2015 08:49 AM 2321 05-21-2015 08:25 AM 2321 05-21-2015 07:21 AM 2321 05-20-2015 02:57 PM -
Activity Feed for Red_Squirrel
- Posted Re: How to format mix of dates and N/A in SAS date format on SAS Programming. 06-12-2015 11:34 AM
- Posted Re: How to format mix of dates and N/A in SAS date format on SAS Programming. 06-11-2015 11:48 AM
- Posted Re: How to convert mix of dates and N/A into SAS date format on SAS Procedures. 06-11-2015 11:06 AM
- Posted How to format mix of dates and N/A in SAS date format on SAS Programming. 06-11-2015 11:02 AM
- Posted How to convert mix of dates and N/A into SAS date format on SAS Procedures. 06-11-2015 10:59 AM
- Posted Re: please help with use of do loops and possibly macros! on SAS Procedures. 05-21-2015 10:08 AM
- Posted Re: please help with use of do loops and possibly macros! on SAS Procedures. 05-21-2015 08:49 AM
- Posted Re: please help with use of do loops and possibly macros! on SAS Procedures. 05-21-2015 08:25 AM
- Posted Re: please help with use of do loops and possibly macros! on SAS Procedures. 05-21-2015 07:21 AM
- Liked Re: please help with use of do loops and possibly macros! for Astounding. 05-20-2015 03:13 PM
- Posted Re: please help with use of do loops and possibly macros! on SAS Procedures. 05-20-2015 02:57 PM
- Posted Re: please help with use of do loops and possibly macros! on SAS Procedures. 05-20-2015 01:48 PM
- Posted Re: please help with use of do loops and possibly macros! on SAS Procedures. 05-20-2015 11:43 AM
- Posted Re: please help with use of do loops and possibly macros! on SAS Procedures. 05-20-2015 10:55 AM
- Posted Re: please help with use of do loops and possibly macros! on SAS Procedures. 05-20-2015 09:55 AM
- Posted please help with use of do loops and possibly macros! on SAS Procedures. 05-20-2015 09:09 AM
-
Posts I Liked
Subject Likes Author Latest Post 4
06-12-2015
11:34 AM
Thank You all for the valuable contributions. None of the suggestions helped unfortunately. I think that's due to the very poor quality level of data in the Excel file. As I am not able to influence production of this file I decided to chang the code logic and get rid of this particular data transformation altogether. Thank you!
... View more
06-11-2015
11:48 AM
Thanks, Aastounding. i Changed this previously as well however I still get the same error message.
... View more
06-11-2015
11:06 AM
it is formatted as TEXT after import and looks this way: 16/04/2015 N/A 21/05/2015 01/06/2015
... View more
06-11-2015
11:02 AM
o Hello, I have and Excel file that I'm reading into SAS. One of the columns has got the mix of dates and N/A values. I'm trying to write this into SAS format of ddmmyy10.0. That's what I have so far in my code: if x = 'N/A' then y = .; else y = input (strip(x), ddmmyy10.0); format y ddmmyy10.0; this returns me the error message: Invalid argument to function INPUT at line.... can can you please advise what needs to be done to correct the issue?
... View more
06-11-2015
10:59 AM
Hello, I have and Excel file that I'm reading into SAS. One of the columns has got the mix of dates and N/A values. I'm trying to write this into SAS format of ddmmyy10.0. That's what I have so far in my code: if x = 'N/A' then y = .; else y = input (strip(x), ddmmyy10.0); format y ddmmyy10.0; this returns me the error message: Invalid argument to function INPUT at line.... can can you please advise what needs to be done to correct the issue?
... View more
05-21-2015
10:08 AM
Thank you very much, Astounding! all works now as it is supposed to!
... View more
05-21-2015
08:49 AM
Certainly: Data want; Set have; If ((value1 < value2 < value3) or (value1 < required_result and value2 < required_result and value3 < required_result)) and (_n_ > 2) and (_n_ > max_recordnum) then do; max_recordnum = _n_ + 3; Saved_average = average1; End; Retain max_recordnum saved_average; If saved_average = . Then required_result = average1; Else required_result = saved_average; Run; IS the problem here because required_result is used in the first if condition before even it has been creared?
... View more
05-21-2015
08:25 AM
Thanks, Astounding. yes, I have already tried and the additional condition does not work. The code results stay unchanged as if there was not any additional condition.
... View more
05-21-2015
07:21 AM
Hi all, I tried to develop on Astounding's code and make sure that I will include the comparison with the newly extended average, however it does not work the way it should be. can you please advise what needs to be done so that condition (value1 < saved_average and value2 < saved_average and value3 < saved_average) always tests against not saved_average but the newly created required_result variable? Data want; Set have; If (value1 < value2 < value3) and (_n_ > 2) and (_n_ > max_recordnum) then do; max_recordnum = _n_ + 3; Saved_average = average1; End; Retain max_recordnum saved_average; If saved_average = . Then required_result = average1; Else required_result = saved_average; If (value1 < saved_average and value2 < saved_average and value3 < saved_average) and (_n_ > max_recordnums) and (_n_ > max_recordnum) then do; max_recordnums = _n_ + 3; Saved_average2 = average1; End; Retain max_recordnums saved_average2; If saved_average2 = . And saved_average = . Then required_result = average1; Else required_result = saved_average; If saved_average2 <> . Then required_result = saved_average2; Run;
... View more
05-20-2015
02:57 PM
Thank you for your time, Astounding, I really appreciate it! I am sorry, I made a silly error typing numbers in on my iPad - in line 15 it should be 27 instead of 7 (please see updated numbers attached). therefore both conditions do not hold for line 15. I would be so grateful if you can help here!
... View more
05-20-2015
01:48 PM
thank you so much, you are a genius, Astounding! That's great for what I explained so far, however what if we had additional condition: - In addition to what we had before, we also have another condition to check (value1 < required_result and value2 < required_result and value3 < required result). If that's true or previous condition (value1 < value2 < value3) is true then required result takes the value of the first encountered average1. As a result, you can see that the RED block has got now the required_result = 5 repeated for 4 rows and then White block immediately after is still using Required_result = 5 as both of our conditions do not hold true here (value1 < required_result and value2 < required_result and value3 < required result) is FALSE and (value1 < value 2 < value3) is also FALSE. The challenge here is that 2nd condition is using the newly created variable required_result. It would be great if you can help! Thank you!
... View more
05-20-2015
11:43 AM
Thanks a lot for trying, Astounding! however, in my example I have numbers instead of "." - I just did not want to write it all up, now I have and attachend a new file to explain. - Green area - first two rows should always have required_result = average1 (without any other logic involved); - Orange area, that's where we start comparing value1< value2 < value3 and if it is true, we need to use the first encountered average1 and use it for the next 4 rows regardless whether condition (value1< value2 < value3) is true or not (That's why it is 7 for all 4 rows). - White area - we continue to use previous average1 value if condition (value1< value2 < value3) is FALSE (that's why it is 7 for rows 8, 9, 10). - Blue area - if condition (value1< value2 < value3) is true then choose the first encountered average1 and use it for the next 4 rows regardless whether condition (value1< value2 < value3) is true or not (that's why it is 24 for the next 4 rows.) - White area - we continue to use previous average1 value if condition (value1 < value2 < value3) is false (that's why it's 24 for another row. i have ended file here however I have a lot more entries down as well). hope it makes sense. thank you!
... View more
05-20-2015
10:55 AM
Hello Xia and all, i think I did not explain my problem quite well before, sorry! I have attached now the picture of the excel file with the sample data (I have only my iPad with me therefore not able to do anything better). ideally what I need is the following: - I need to work out the required_result column using the following logic: - for row 2 and 3 select average1 value (without using any other logic). Therefore required_result = 2 for row 2 and required_result = 3 for row 3; - for all other rows use the following logic: if value1 < value2 < value3 then use the first encountered value of the average1 for at least 4 rows (7 in this case) - continue using 7 after 4 rows if the following is FALSE - value1 < value2 < value3; - if after 4 rows above the following is TRUE - value1 < value2 < value3, then use next first encountered average1 value (24 in this case) for at least 4 rows and then repeat the logic. thank you!
... View more
05-20-2015
09:55 AM
Apologies, Xia, quick typing! I obviously meant value1 < value2 < value3 is your solution still going to be ok for this logic? I will try it out and let you know. thank you!
... View more
05-20-2015
09:09 AM
Hello all, I am quite new to SAS programming therefore would appreciate any input on the possible solution of the problem I have! I have the following data value1 value2 value3 average1 required_result 1 9 12 7 7 8 6 9 6 7 4 7 10 2 7 6 2 1 8 7 . . . . . . . . . . . . 7 9 16 24 24 . . . . 24 X Y Z W I need to do the following: - If row n has got the following trend - value1 > value2> value3, then use the first encountered average1 value for the next 4 rows; - after 4 rows calculate if value1 > value2 > value3 is true. If it is true then use the next first encountered average1 value for another 4 rows; - repeat. for example, in the first row value1 > value2> value3 (1 > 9 > 12) therefore we need to use average1 = 7 the next 4 rows starting from the first one. can you please advise how this can be coded in SAS? thank you so much for all your help!
... View more