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: Why Proc contents does not show the number of observations in my d...

by Quartz | Level 8 in SAS Procedures
‎08-23-2013 08:58 AM
‎08-23-2013 08:58 AM
Could he use sql pass thru and run a query to count the number of rows and convert the results to character before they are passed back to SAS? You could also try the system table all_tables  column num_rows however this isn't always up to date. ... View more

Re: Format Date to DD-MMM-YY

by Quartz | Level 8 in SAS Programming
‎08-23-2013 06:54 AM
‎08-23-2013 06:54 AM
PROC FORMAT; PICTURE DDMMMYYD   LOW - HIGH = '%0d-%b-%y' (DATATYPE=DATE); RUN; DATA HAVE; INFILE DATALINES DLM=","; INPUT DATES ; INFORMAT DATES DATE9.; FORMAT DATES DATE9.; DATALINES; 01JUL2013 01AUG2013 01SEP2013 01NOV2013 01DEC2013 01JAN2014 ; RUN; DATA WANT; %LET _EFIERR_ = 0; %LET _EFIREC_ = 0;   FILE "E:\WANT.CSV" DSD DLM=",";   IF _N_ = 1 THEN DO;   PUT "DATES" "," "OBS";   END;   SET HAVE;   OBS+1;   FORMAT DATES DDMMMYYD.;   PUT DATES @;   PUT OBS;   IF _ERROR_ THEN CALL SYMPUTX('_EFIERR_',1);   IF EFIEOD THEN CALL SYMPUTX('_EFIREC_',EFIOUT); RUN; ... View more

Re: Extracting Data From Webpages

by Quartz | Level 8 in SAS Programming
‎08-22-2013 06:10 AM
‎08-22-2013 06:10 AM
Hi FriedEgg, Thank you for that additional information. Another question if I could. Before going to each of the individual search results I would like to compile a list of every property returned by the search itself (currently this would be carried out on a page by page basis) by using the value associated with the next button,  I assume I could do this using an iterative do loop in this instance, but I would prefer to use the next buttons value in case I have a future requirement to do so.  Sorry to be a pain.  I rarely use txt files, so am using this scenario to build my skills. Thanks again for your help. Regards, Scott ... View more

Re: Discussion on Proc transpose

by Quartz | Level 8 in SAS Data Science
‎08-22-2013 02:06 AM
‎08-22-2013 02:06 AM
Glad that I could help.  Good luck with the rest of your task. ... View more

Re: Export Proc Contents

by Quartz | Level 8 in ODS and Base Reporting
‎08-21-2013 07:57 PM
‎08-21-2013 07:57 PM
You just need an out=<dsname> in your code and then export it to excel in the usual manner. proc contents data=sashelp.class out= want; run; ... View more

Re: Extracting Data From Webpages

by Quartz | Level 8 in SAS Programming
‎08-20-2013 09:25 PM
‎08-20-2013 09:25 PM
That is an outstanding solution.  Thank you very much for taking the time to code it. I understand everything in the code except for the need to define the hash object as a hash iterator.  Can I ask why this is the case? Thank you again for your help and for Vince's suggestion. ... View more

Extracting Data From Webpages

by Quartz | Level 8 in SAS Programming
‎08-20-2013 02:58 AM
‎08-20-2013 02:58 AM
Hi Guys, I am presently in the process of scraping data from a webpage using the following code: FILENAME FOO URL 'http://www.realestate.com.au/rent/in-southport%2c+qld+4215%3b+labrador%2c+qld+4215%3b+bundall%3b+benowa%2c+qld+4217%3b+benowa+waters%2c+qld+4217/list-1?includeSurrounding=false'; DATA _NULL_;   INFILE FOO LENGTH=LEN LRECL=200000;   RETAIN NEXT;   INPUT RECORD $VARYING25000. LEN;   RECORD = COMPRESS(RECORD);   IF FIND(UPCASE(RECORD),'<LINKREL="NEXT"HREF=') THEN DO;   START  = FIND(UPCASE(RECORD),'<LINKREL="NEXT"HREF=') + LENGTH('"<LINKREL="NEXT"HREF=');   ADDLEN = FIND(UPCASE(RECORD),"/><LINKREL='STYLESHEET'")-1 - START;   NEXT   = SUBSTR(RECORD,START,ADDLEN);   END;   IF UPCASE(RECORD) =: "DATA-FEATURED";   START  = FIND(UPCASE(RECORD),"/",FIND(UPCASE(RECORD),"<H2><AHREF="));   ADDLEN = FIND(UPCASE(RECORD),"'CLASS=") - START;   URL = "http://www.realestate.com.au"||SUBSTR(RECORD,START,ADDLEN);   I +1;   DROP RECORD I;   CALL EXECUTE("FILENAME FOO URL '" || COMPRESS(URL) || "';");   CALL EXECUTE("DATA PAGE" || COMPRESS(I) || ";   INFILE FOO LENGTH=LEN LRECL=60000;   INPUT RECORD $VARYING25000. LEN;   RUN;");   /*IF EOF THEN CALL EXECUTE("FILENAME FOO URL '" || COMPRESS(NEXT) || "';");*/ RUN; It works as expected, but the next step is to alter my CALL EXECUTE that will extract data from the relevant pages captured by the _NULL_ datastep.  It appears that all the data I am interested in is contained within the 12th observation in tables PAGE1 - PAGE20.  Although I am interested in many of the items contained within this variable, I am most interested in Prices, Bedrooms, Car Spaces and Bathrooms. Does anyone know it there is an alternative to a dozen SUBSTR functions to extract the data I am interested in? In addition to this, I would also like to iterate through the search result pages simulating the use of the webpage "NEXT" button.  Although I have been able to do this on a one off basis, I need to do so after the last record in the _NULL_ datastep has been processed.  I can't use the END = INFILE option, because of the subsetting IF's.  Does anyone have an alternative that I could employ in this instance? Thank you very much for your help. Regards, Scott ... View more

Re: Why Proc contents does not show the number of observations in my d...

by Quartz | Level 8 in SAS Procedures
‎08-19-2013 07:59 PM
‎08-19-2013 07:59 PM
I was hoping for an error or a missing value, as opposed to -1 for number of observations not available. I can't believe there isn't an accurate way to identify the number of observations in a dataset that size.  ... View more

Re: SAS access Google/Yahoo search engine to colect url lists

by Quartz | Level 8 in SAS Programming
‎08-18-2013 06:40 AM
‎08-18-2013 06:40 AM
Hi Tiffany, Can you please post when you receive a successful outcome?  I could be very interested to see how things progress. Regards, Scott ... View more

Re: Why Proc contents does not show the number of observations in my d...

by Quartz | Level 8 in SAS Procedures
‎08-17-2013 10:06 PM
‎08-17-2013 10:06 PM
You also need to change WORK.NEWFILES to the name of your dataset LOCATION.HUGE_DATA_SET.  Probably self evident, but just in case. %LET DSID  = %SYSFUNC(OPEN(WORK.NEWFILES,I)); %LET TOTAL = %SYSFUNC(ATTRN(&DSID.,NOBS)); %PUT the number of observations is &TOTAL; ... View more

Re: Why Proc contents does not show the number of observations in my d...

by Quartz | Level 8 in SAS Procedures
‎08-17-2013 02:07 AM
‎08-17-2013 02:07 AM
What happens if you use the following? %LET DSID  = %SYSFUNC(OPEN(WORK.NEWFILES,I)); %LET TOTAL = %SYSFUNC(ATTRN(&DSID.,NOBS)); Does the macro variable store the correct number of observations? ... View more

Re: DATASTEP summary

by Quartz | Level 8 in SAS Programming
‎08-15-2013 05:58 AM
‎08-15-2013 05:58 AM
Another solution: ODS OUTPUT ONEWAYFREQS=WORK.OWF (DROP = F_: RENAME = (FREQUENCY = COUNT)); PROC FREQ DATA=HAVE;   BY DOCUMENTATION_VV OCCUPANCY_VV PROPERTY_VV PURPOSE_VV;   TABLES DOCUMENTATION_INVALID OCCUPANCY_INVALID PROPERTY_INVALID PURPOSE_INVALID /NOCUM NOPERCENT ; RUN; ODS OUTPUT CLOSE; DATA TRANSFORM (KEEP = VAL_INVAL VARIABLE VALID_VALUES DOCUMENTATION_VV COUNT);   LENGTH  VAL_INVAL $7. VARIABLE $21.;   VAL_INVAL = "VALID";   SET OWF;   BY TABLE;   IF DOCUMENTATION_INVALID = 1 THEN VAL_INVAL = "INVALID";   ELSE IF OCCUPANCY_INVALID = 1 THEN VAL_INVAL = "INVALID";   ELSE IF PROPERTY_INVALID = 1 THEN VAL_INVAL = "INVALID";   ELSE IF PURPOSE_INVALID = 1 THEN VAL_INVAL = "INVALID";   VARIABLE = SCAN(TABLE,-1);   IF VARIABLE = "DOCUMENTATION_INVALID" THEN VALID_VALUES = DOCUMENTATION_VV;   ELSE IF VARIABLE = "OCCUPANCY_INVALID" THEN VALID_VALUES = OCCUPANCY_VV;   ELSE IF VARIABLE = "PROPERTY_INVALID" THEN VALID_VALUES = PROPERTY_VV;   ELSE IF VARIABLE = "PURPOSE_INVALID" THEN VALID_VALUES = PURPOSE_VV;   OUTPUT;   IF FIRST.TABLE THEN TOTAL = COUNT;     ELSE TOTAL+COUNT;   IF LAST.TABLE  THEN DO;     COUNT = TOTAL;     VAL_INVAL = "TOTAL";     OUTPUT;   END; RUN; PROC TRANSPOSE DATA = TRANSFORM OUT=TRANS (DROP = DOCUMENTATION_VV _:) SUFFIX=_COUNT;   ID VAL_INVAL ;   BY VARIABLE VALID_VALUES DOCUMENTATION_VV NOTSORTED;   VAR COUNT; RUN; ... View more

Re: join on 3 columns

by Quartz | Level 8 in SAS Procedures
‎08-14-2013 07:32 AM
‎08-14-2013 07:32 AM
DATA ACCT_DTL ; INFILE DATALINES; INPUT TLNBR; DATALINES; 1 2 3 4 5 6 7 8 9 ; RUN; DATA CONS3; INFILE DATALINES DELIMITER="," TRUNCOVER; INPUT HOME BUSI ALT; DATALINES; 1,1,1 1,1, 3,3, 5, ,5 7,, 9,9,9 9,9,9 15,12,37 10,10,7 11,9,11 12,12,12 20,9,30 20,20,20 ; RUN; /*CREATE MACRO VARIABLE NUMVARS CONTAINNIG ALL NUMERIC VARIABLES FOR THE SPECIFYING THE ARRAY*/ PROC SQL NOPRINT;   SELECT NAME INTO : NUMVARS SEPARATED BY " "   FROM DICTIONARY.COLUMNS   WHERE LIBNAME = "WORK" AND MEMNAME = "CONS3" AND UPCASE(TYPE) = "NUM"; RUN; /*IF YOU WANT A LIST OF OBSERVATIONS THAT HAVE AT LEAST ONE VALUE THAT DOESN'T APPEAR IN ACCT_DTL*/ DATA WANT1;   IF _N_ = 1 THEN DO;   DECLARE HASH H(DATASET:"ACCT_DTL");   H.DEFINEKEY("TLNBR");   H.DEFINEDATA("TLNBR");   H.DEFINEDONE();   END;   ARRAY VARS {*} &NUMVARS.;   DO UNTIL (EOF);   SET CONS3 END=EOF;   DO I = LBOUND(VARS) TO HBOUND(VARS);   TLNBR = VARS{I};   IF H.FIND() ~= 0 THEN DO;   RETURN;   END;   END;   END;   RC=H.CLEAR();   DROP I RC TLNBR; RUN; /*IF YOU WANT A LIST OF OBSERVATIONS THAT HAVE NO VALUES CONTAINED IN  ACCT_DTL*/ DATA WANT2;   IF _N_ = 1 THEN DO;   DECLARE HASH H(DATASET:"ACCT_DTL");   H.DEFINEKEY("TLNBR");   H.DEFINEDATA("TLNBR");   H.DEFINEDONE();   END;   ARRAY VARS {*} &NUMVARS.;   DO UNTIL (EOF);   SET CONS3 END = EOF;   DO I = 1 TO DIM(VARS);   TLNBR = VARS{I};   IF H.FIND() = 0 THEN DO;   I = HBOUND(VARS);   END;   ELSE IF H.FIND() ~= 0  AND HBOUND(VARS) = I THEN DO;   OUTPUT;   END;   END;   END;   RC=H.CLEAR();   DROP I RC TLNBR; RUN; ... View more

Re: Discussion on Proc transpose

by Quartz | Level 8 in SAS Data Science
‎08-14-2013 05:26 AM
‎08-14-2013 05:26 AM
How about this? Create test scenario. DATA HAVE; LENGTH VDSID_HHID $40. ITEM_NAME $40.; INFILE DATALINES DELIMITER=","; INPUT  VDSID_HHID $ ITEM_NAME $ QUANTITY TOTAL_VALUE; DATALINES; INMHKAL100213000,SORGHUM,66,528 INMHKAL100213000,WHEAT,15,75 INMHKAL100213000,RICE,10,60 INMHKAL100213000,PIGEONPEA,4,119 INMHKAL100213000,CHICKPEA,1,12 INMHKAL100213000,MATKI/COWPEA,1,28 INMHKAL100213000,EDIBLE OIL,4,208 INMHKAL100213000,ALL TYPES OF VEGETABLES,0,115 INMHKAL100213000,ALL TYPES OF FRUITS,0,30 INMHKAL100214000,PDS RICE,10,60 INMHKAL100214000,SORGHUM,165,1155 INMHKAL100214000,WHEAT,20,100 INMHKAL100214000,PIGEONPEA,5,133 INMHKAL100214000,CHICKPEA,2,48 INMHKAL100214000,MATKI/COWPEA,4,94.5 INMHKAL100214000,GROUNDNUT OIL,6,276 INMHKAL100214000,ALL TYPES OF VEGETABLES,0,70 INMHKAL100214000,ALL TYPES OF FRUITS,216,216 RUN; Summarise multiple observations with the same VDSID_HHID and ITEM_NAME. PROC SUMMARY DATA=HAVE NWAY MISSING;   CLASS VDSID_HHID ITEM_NAME;   VAR QUANTITY TOTAL_VALUE;   OUTPUT OUT=HAVESUM (drop = _:) SUM=; RUN; Transpose data to have a single column called COL1 containing the values of both QUANTITY and  TOTAL_VALUE and a variable called _NAME_ containing the variable names transposed into COL1. PROC TRANSPOSE DATA=HAVESUM OUT=TRANSPOSE; BY ITEM_NAME VDSID_HHID NOTSORTED; VAR QUANTITY TOTAL_VALUE; RUN; Transpose the data a second time, using the ITEM_NAME and _NAME_ variables in the ID statement to name the created variables in a format similar to what you desired. PROC TRANSPOSE DATA=TRANSPOSE OUT=TRANSPOSE1 (DROP=_NAME_) DELIMITER=_; ID ITEM_NAME _NAME_; VAR COL:; BY VDSID_HHID NOTSORTED; RUN; You can now merge your 2 datasets. You could do something similar in a datastep, however I am sure you know how to create a series of IF ELSE statements. Regards, Scott ... View more

Re: holding SAS value using @

by Quartz | Level 8 in SAS Procedures
‎08-13-2013 01:51 AM
1 Like
‎08-13-2013 01:51 AM
1 Like
From what I understand, using the ampersand special character/modifier won't help you anyway.  When using this modifier SAS treats two or more spaces as the end of the string, which you don't have in your 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