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: 
Scott_Mitchell
‎09-28-2023
  • 328 Posts
  • 39 Likes Given
  • 1 Solutions
  • 150 Likes Received

Re: How to rank observations in an arbitrary way?

by Quartz | Level 8 in SAS Procedures
‎09-09-2013 10:36 PM
1 Like
‎09-09-2013 10:36 PM
1 Like
You could use proc format. PROC FORMAT; VALUE PERRANK LOW  -< 0.1 = '1' 0.1  -< 0.2 = '2' 0.2  -< 0.3 = '3' 0.3  -< 0.4 = '4' 0.4  -< 0.5 = '5' 0.5  -< 0.6 = '6' 0.6  -< 0.7 = '7' 0.7  -< 0.8 = '8' 0.8  -< 0.9 = '9' 0.9  -< 1.0 = '10' ; RUN; DATA HAVE; DO I = 1 TO 100000; PERCENT = RANUNI(0); OUTPUT; END; RUN; DATA WANT; SET HAVE; RANK = PUT(PERCENT,PERRANK.); RUN; ... View more

Re: Taking a mean within a datetime interval

by Quartz | Level 8 in SAS Programming
‎09-05-2013 11:44 PM
‎09-05-2013 11:44 PM
Hi CTW, Ok, let's work through this together and see what we come up with.  I have created a larger sample of data with the following which transcends hours, using the following: DATA HAVE; INFILE DATALINES DLM=','; INPUT TIME_STAMP; INFORMAT TIME_STAMP ANYDTDTM19.; FORMAT TIME_STAMP DATETIME20.; AVGTEMP = RANUNI(0)*100; DATALINES; 10/07/2013 01:00:00 10/07/2013 01:01:00 10/07/2013 01:02:00 10/07/2013 01:03:00 10/07/2013 01:04:00 10/07/2013 01:05:00 10/07/2013 01:06:00 10/07/2013 01:07:00 10/07/2013 01:08:00 10/07/2013 01:09:00 10/07/2013 01:10:00 10/07/2013 01:11:00 10/07/2013 01:12:00 10/07/2013 01:13:00 10/07/2013 01:14:00 10/07/2013 01:15:00 10/07/2013 01:16:00 10/07/2013 01:17:00 10/07/2013 01:18:00 10/07/2013 01:19:00 10/07/2013 01:20:00 10/07/2013 01:21:00 10/07/2013 01:22:00 10/07/2013 01:23:00 10/07/2013 01:24:00 10/07/2013 01:25:00 10/07/2013 01:26:00 10/07/2013 01:27:00 10/07/2013 01:28:00 10/07/2013 01:29:00 10/07/2013 01:30:00 10/07/2013 01:31:00 10/07/2013 01:32:00 10/07/2013 01:33:00 10/07/2013 01:34:00 10/07/2013 01:35:00 10/07/2013 01:36:00 10/07/2013 01:37:00 10/07/2013 01:38:00 10/07/2013 01:39:00 10/07/2013 01:40:00 10/07/2013 01:41:00 10/07/2013 01:42:00 10/07/2013 01:43:00 10/07/2013 01:44:00 10/07/2013 01:45:00 10/07/2013 01:46:00 10/07/2013 01:47:00 10/07/2013 01:48:00 10/07/2013 01:49:00 10/07/2013 01:50:00 10/07/2013 01:51:00 10/07/2013 01:52:00 10/07/2013 01:53:00 10/07/2013 01:54:00 10/07/2013 01:55:00 10/07/2013 01:56:00 10/07/2013 01:57:00 10/07/2013 01:58:00 10/07/2013 01:59:00 10/07/2013 02:00:00 10/07/2013 02:01:00 10/07/2013 02:02:00 10/07/2013 02:03:00 10/07/2013 02:04:00 10/07/2013 02:05:00 10/07/2013 02:06:00 10/07/2013 02:07:00 10/07/2013 02:08:00 10/07/2013 02:09:00 10/07/2013 02:10:00 10/07/2013 02:11:00 10/07/2013 02:12:00 10/07/2013 02:13:00 10/07/2013 02:14:00 10/07/2013 02:15:00 10/07/2013 02:16:00 10/07/2013 02:17:00 10/07/2013 02:18:00 10/07/2013 02:19:00 10/07/2013 02:20:00 10/07/2013 02:21:00 10/07/2013 02:22:00 10/07/2013 02:23:00 10/07/2013 02:24:00 10/07/2013 02:25:00 10/07/2013 02:26:00 10/07/2013 02:27:00 10/07/2013 02:28:00 10/07/2013 02:29:00 10/07/2013 02:30:00 10/07/2013 02:31:00 10/07/2013 02:32:00 10/07/2013 02:33:00 10/07/2013 02:34:00 10/07/2013 02:35:00 10/07/2013 02:36:00 10/07/2013 02:37:00 10/07/2013 02:38:00 10/07/2013 02:39:00 10/07/2013 02:40:00 10/07/2013 02:41:00 10/07/2013 02:42:00 10/07/2013 02:43:00 10/07/2013 02:44:00 10/07/2013 02:45:00 10/07/2013 02:46:00 10/07/2013 02:47:00 10/07/2013 02:48:00 10/07/2013 02:49:00 10/07/2013 02:50:00 10/07/2013 02:51:00 10/07/2013 02:52:00 10/07/2013 02:53:00 10/07/2013 02:54:00 10/07/2013 02:55:00 10/07/2013 02:56:00 10/07/2013 02:57:00 10/07/2013 02:58:00 10/07/2013 02:59:00 10/07/2013 03:00:00 10/07/2013 03:01:00 10/07/2013 03:02:00 10/07/2013 03:03:00 10/07/2013 03:04:00 10/07/2013 03:05:00 10/07/2013 03:06:00 10/07/2013 03:07:00 10/07/2013 03:08:00 10/07/2013 03:09:00 10/07/2013 03:10:00 10/07/2013 03:11:00 10/07/2013 03:12:00 10/07/2013 03:13:00 10/07/2013 03:14:00 10/07/2013 03:15:00 10/07/2013 03:16:00 10/07/2013 03:17:00 10/07/2013 03:18:00 10/07/2013 03:19:00 10/07/2013 03:20:00 10/07/2013 03:21:00 10/07/2013 03:22:00 10/07/2013 03:23:00 10/07/2013 03:24:00 10/07/2013 03:25:00 10/07/2013 03:26:00 10/07/2013 03:27:00 10/07/2013 03:28:00 10/07/2013 03:29:00 10/07/2013 03:30:00 10/07/2013 03:31:00 10/07/2013 03:32:00 10/07/2013 03:33:00 10/07/2013 03:34:00 10/07/2013 03:35:00 10/07/2013 03:36:00 10/07/2013 03:37:00 10/07/2013 03:38:00 10/07/2013 03:39:00 10/07/2013 03:40:00 10/07/2013 03:41:00 10/07/2013 03:42:00 10/07/2013 03:43:00 10/07/2013 03:44:00 10/07/2013 03:45:00 10/07/2013 03:46:00 10/07/2013 03:47:00 10/07/2013 03:48:00 10/07/2013 03:49:00 10/07/2013 03:50:00 10/07/2013 03:51:00 10/07/2013 03:52:00 10/07/2013 03:53:00 10/07/2013 03:54:00 10/07/2013 03:55:00 10/07/2013 03:56:00 10/07/2013 03:57:00 10/07/2013 03:58:00 10/07/2013 03:59:00 10/07/2013 04:00:00 ; RUN; I then created the following which will display the ten minute intervals and associated TIME_STAMP values. DATA TEST;   SET HAVE;   TIME_STAMP1 = INTNX('DTMINUTE10.',TIME_STAMP,0);   FORMAT TIME_STAMP1 DATETIME20.; RUN; In this data set you should observe that each TIME_STAMP1 has 10 values, except 10JUL2013:04:00:00 having only 1 which is expected given the sample.  Looking closer at the data you should see that each grouping created by the INTNX function contains the ranges 00-09 10-19 20-29 30-39 40-49 50-59, regardless of the hour, therefore this doesn't appear to be the cause of the issue you are describing. Next I altered the code in the HASH OBJECT to allow us to see the number of records being matched by changing  HH.DEFINEDATA ('TIME_STAMP1','AVG_TEMP') ; to   HH.DEFINEDATA ('TIME_STAMP1','AVG_TEMP','COUNT') ;  It is sort of like a keep statement and allows us to see the variable COUNT. DATA _NULL_;   FORMAT TIME_STAMP1 DATETIME20.;   DECLARE HASH HH (ORDERED:'YES');   HH.DEFINEKEY ('TIME_STAMP1') ;   HH.DEFINEDATA ('TIME_STAMP1','AVG_TEMP','COUNT') ;   HH.DEFINEDONE () ;   DO UNTIL (DONE);   SET HAVE END=DONE;   TIME_STAMP1 = INTNX('DTMINUTE10.',TIME_STAMP,0);   IF HH.FIND() ~= 0 THEN DO;   COUNT    = 1;   SUMTEMP  = AVGTEMP;   AVG_TEMP = SUMTEMP/COUNT;   HH.ADD();   END;   ELSE IF HH.FIND() = 0 THEN DO;   COUNT+1;   SUMTEMP  = SUM(SUMTEMP,AVGTEMP);   AVG_TEMP = SUMTEMP/COUNT;   HH.REPLACE();   END;   END;   HH.OUTPUT (DATASET: 'WANT') ; RUN; The result shows that we have added or matched 10 records for each distinct TIME_STAMP1 which is what we expect.   I then did a PROC SUMMARY on the TEST dataset we created previously and merged the results against the HASH TABLE output. PROC SUMMARY DATA = TEST NWAY MISSING;   CLASS TIME_STAMP1;   VAR AVGTEMP;   OUTPUT OUT = MEANS (DROP = _:) MEAN=; RUN; DATA RECONCILE;   MERGE WANT    MEANS;   BY TIME_STAMP1;   IF AVGTEMP=AVGTEMP THEN MATCH = 1; RUN; I get matches in every instance, so I am not sure what the problem is on your end. Perhaps use the method I have employed above on your dataset and see what outcomes it produces, otherwise post a sample of the data causing your problem and I will have a look at it for you. Regards, Scott ... View more

Re: Taking a mean within a datetime interval

by Quartz | Level 8 in SAS Programming
‎09-04-2013 12:56 AM
‎09-04-2013 12:56 AM
Since I have been experimenting with HASH OBJECTS of late, I thought this might be a good time to get some feedback on my code on this question. DATA _NULL_;   FORMAT TIME_STAMP1 DATETIME20.;   DECLARE HASH HH (ORDERED:'YES');   HH.DEFINEKEY ('TIME_STAMP1') ;   HH.DEFINEDATA ('TIME_STAMP1','AVG_TEMP') ;   HH.DEFINEDONE () ;   DO UNTIL (DONE);   SET HAVE END=DONE;   TIME_STAMP1 = INTNX('DTMINUTE10.',TIME_STAMP,0);   IF HH.FIND() ~= 0 THEN DO;   COUNT    = 1;   SUMTEMP  = AVGTEMP;   AVG_TEMP = SUMTEMP/COUNT;   HH.ADD();     END;   ELSE IF HH.FIND() = 0 THEN DO;   COUNT+1;   SUMTEMP  = SUM(SUMTEMP,AVGTEMP);   AVG_TEMP = SUMTEMP/COUNT;   HH.REPLACE();       END;   END;   HH.OUTPUT (DATASET: 'WANT') ; RUN; ... View more

Re: Taking a mean within a datetime interval

by Quartz | Level 8 in SAS Programming
‎09-03-2013 11:17 PM
‎09-03-2013 11:17 PM
I assume from your question that you are aware of how to calculate the mean and that your issue is purely the datetime.  The following should help. DATA HAVE; INFILE DATALINES DLM=','; INPUT TIME_STAMP AVGTEMP; INFORMAT TIME_STAMP ANYDTDTM19.; FORMAT TIME_STAMP DATETIME20.; DATALINES; 10/07/2013 01:00:00,36.631 10/07/2013 01:01:00,36.632 10/07/2013 01:02:00,36.647 10/07/2013 01:03:00,36.659 10/07/2013 01:04:00,36.664 10/07/2013 01:05:00,36.664 10/07/2013 01:06:00,36.664 10/07/2013 01:07:00,36.664 10/07/2013 01:08:00,36.664 10/07/2013 01:09:00,36.664 10/07/2013 01:10:00,36.664 10/07/2013 01:11:00,36.664 10/07/2013 01:12:00,36.664 10/07/2013 01:13:00,36.664 10/07/2013 01:14:00,36.664 10/07/2013 01:15:00,36.664 10/07/2013 01:16:00,36.664 ; RUN; DATA WANT;   SET HAVE;   TIME_STAMP1 = INTNX('DTMINUTE10.',TIME_STAMP,0);   FORMAT TIME_STAMP1 DATETIME20.; RUN; ... View more

Re: count if base on row calculation

by Quartz | Level 8 in SAS Programming
‎09-02-2013 01:31 AM
3 Likes
‎09-02-2013 01:31 AM
3 Likes
By statement processing will do the trick here. PROC SORT DATA=HAVE;   BY ID PRICE; RUN; DATA WANT;   SET HAVE;   BY ID PRICE;   IF FIRST.ID THEN COLUMN3 = 0;      ELSE COLUMN3+1; RUN; ... View more

Re: match data find means

by Quartz | Level 8 in SAS Programming
‎09-02-2013 01:10 AM
‎09-02-2013 01:10 AM
Hi LanMin, Is this what you are trying to achieve? PROC MEANS DATA=HAVE NONOBS NOPRINT NWAY MISSING; CLASS INDUSTRY; VAR DISCLOSE; OUTPUT OUT=WANT (DROP =_:) MEAN=; RUN; Regards, Scott ... View more

Re: update vs proc sql

by Quartz | Level 8 in SAS Programming
‎09-01-2013 10:16 PM
1 Like
‎09-01-2013 10:16 PM
1 Like
OP, I have replicated Tom's method and it results in exactly what you are after. DATA WANT;   MERGE MASTER        TRANS (RENAME = (VALUE = NEWVALUE));   BY NAME;   IF MISSING(NEWVALUE) THEN NEWVALUE = VALUE;   DROP VALUE;   RENAME NEWVALUE = VALUE; RUN; What seems to be the problem? ... View more

Re: How would I go about importing Serveral Tables in a single Excel s...

by Quartz | Level 8 in SAS Procedures
‎09-01-2013 09:59 PM
‎09-01-2013 09:59 PM
Hi Mitchy, As has mentioned there are many approaches you can take in this situation. A simple method is using EXCEL Ranges and PROC COPY.  I am using PCFILE SERVER to access the EXCEL Workbook.  If you aren't using the PC FILE SERVER, you just need to change the LIBNAME statement to use your preferred method.  I would suggest that you maintain the acceptable SAS naming conventions when importing the data for named ranges, however if you are unable to do so you will need to use SAS name literals, which means enclosing your name in single quotes  followed by an N  e.g. 'SUM TOTAL'N.  I have other methods to correct for names that do not meet the SAS naming conventions, however they are more complicated and perhaps irreverent in your scenario. The following link shows how to create a dynamic in EXCEL range to ensure that you are capturing all your data when additional rows are added. How to create a dynamic defined range in an Excel worksheet LIBNAME XLSFILE PCFILES TYPE=EXCEL PORT=8621 SERVER=LOCALHOST PATH = "E:\NEWMICROSOFTEXCELWORKSHEET.XLSX"; PROC COPY INLIB=XLSFILE OUTLIB=WORK; /*THE LIBRARIES WHERE THE DATA ARE PRESENTLY LOCATED - INLIB AND THE OUTPUT LIBRARY - OUTLIB */   SELECT SAMPLERANGE TESTRANGE VARRANGE;/*THE NAMES OF  THE RANGES YOU WISH TO IMPORT - NAMES ARE RETAINED IN THE OUTLIB*/ RUN; LIBNAME XLSFILE CLEAR; There are more dynamic ways of importing all ranges, however given you will have to set up the ranges in EXCEL initially anyway, it is probably overkill in this situation. Regards, Scott ... View more

Re: Identifying Date, Currency and Time variables

by Quartz | Level 8 in SAS Procedures
‎08-29-2013 06:41 AM
‎08-29-2013 06:41 AM
It depends what you are trying to do.  If you just want a quick list of how the data is presented in the file you could use; proc sql; describe table sashelp.cars; quit; This will describe the table's columns in the log. If you want the information output to a dataset; proc contents data=sashelp.cars out=want; run; or proc sql; create table dset_details as select * from dictionary.columns where libname = "SASHELP" and memname = 'CARS'; quit; The format used can be found in the format variable. Regards, Scott ... View more

Re: How to set sampsize as count of table/4

by Quartz | Level 8 in SAS Procedures
‎08-28-2013 07:53 AM
3 Likes
‎08-28-2013 07:53 AM
3 Likes
You could create a macro variable based on the number of obs in the dataset using the following. %LET DSID = %SYSFUNC(OPEN(SASHELP.CLASS)); %LET NUMOBS = %SYSFUNC(ATTRN(&DSID.,NOBS)); %LET SAMP = %SYSEVALF(&NUMOBS./4,CEIL); %LET RC   = %SYSFUNC(CLOSE(&DSID)); %PUT *****************************&NUMOBS. &SAMP.; Edited to make use of the CEIL modifier. ... View more

Re: Update all cell in table based on conditions

by Quartz | Level 8 in SAS Procedures
‎08-28-2013 07:36 AM
1 Like
‎08-28-2013 07:36 AM
1 Like
Glad I could help. ... View more

Re: reading NO Delimited Data

by Quartz | Level 8 in SAS Programming
‎08-28-2013 06:30 AM
‎08-28-2013 06:30 AM
It is hard to assume anything based on the volume of data you have provided. Are the fields supposed to be fixed width?  If not, how do you know where each field ends? The following should be pretty self explanatory. data want; infile datalines; INPUT @1 var1 $2.       var2 6.       var3 $8. ; datalines; BE123456AAA NL987654BBBBBBB ; run; ... View more

Re: Update all cell in table based on conditions

by Quartz | Level 8 in SAS Procedures
‎08-28-2013 05:52 AM
4 Likes
‎08-28-2013 05:52 AM
4 Likes
If you only want to change the presentation of the missing value, you could use the missing = option: options missing='0'; data have; input week pert sum tar west; datalines; 18 200  8400 . 100 19 . 6970 6720 . 20 200 . 9766  . 21 .  8400 . 390 22 100 . 8400 . ; run; ... View more

Re: Update all cell in table based on conditions

by Quartz | Level 8 in SAS Procedures
‎08-28-2013 05:48 AM
1 Like
‎08-28-2013 05:48 AM
1 Like
DATA WANT; SET HAVE; ARRAY NVARS _NUMERIC_; DO I = 1 TO DIM(NVARS); NVARS{I} = TRANSLATE(NVARS{I},0,.); END; RUN; ... View more

Re: reading NO Delimited Data

by Quartz | Level 8 in SAS Programming
‎08-28-2013 05:25 AM
‎08-28-2013 05:25 AM
Can you provide some sample data? ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎09-28-2023 08:59 AM
Group Hubs for Scott_Mitchell
View All
Likes given to
Likes from
Quick links