About LarryWorley

LarryWorley · ‎08-15-2013

So does your output look something like the following. I created a 10 record dataset and made a copy of it. In the copy I deleted the fifth record and changed the values of the non-id variable on the last five variables. The COMPARE Procedure Comparison of WORK.X with WORK.Y (Method=EXACT) Data Set Summary Dataset Created Modified NVar NObs WORK.X 15AUG13:17:47:52 15AUG13:17:47:52 2 10 WORK.Y 15AUG13:18:10:07 15AUG13:18:10:07 2 9 Variables Summary Number of Variables in Common: 2. Number of ID Variables: 1. Observation Summary Observation Base Compare ID First Obs 1 1 i=1 First Unequal 6 5 i=6 Last Unequal 10 9 i=10 Last Obs 10 9 i=10 Number of Observations in Common: 9. Number of Observations in WORK.X but not in WORK.Y: 1. Total Number of Observations Read from WORK.X: 10. Total Number of Observations Read from WORK.Y: 9. Number of Observations with Some Compared Variables Unequal: 5. Number of Observations with All Compared Variables Equal: 4. Values Comparison Summary Number of Variables Compared with All Observations Equal: 0. Number of Variables Compared with Some Observations Unequal: 1. Total Number of Values which Compare Unequal: 5. Maximum Difference: 10. Comparison of WORK.X with WORK.Y (Method=EXACT) All Variables Compared have Unequal Values Variable Type Len Ndif MaxDif j NUM 8 5 10.000 Value Comparison Results for Variables _________________________________________________________ || Base Compare i || j j Diff. % Diff _______ || _________ _________ _________ _________ || 6 || 120.0000 130.0000 10.0000 8.3333 7 || 140.0000 150.0000 10.0000 7.1429 8 || 160.0000 170.0000 10.0000 6.2500 9 || 180.0000 190.0000 10.0000 5.5556 10 || 200.0000 210.0000 10.0000 5.0000 _________________________________________________________ Take a look at the output dataset options from proc compare and see if there is an output datset which you could post process to make a more compact report. The raw output from proc compare can be a little overwhelming when there are many data discrepencies.

LarryWorley · ‎08-15-2013

index function apparently does not trim trailing blanks from the value of a variable used as a parameter. That behavior is probably what should be expected. In your first step, the data compiletr creates b with a length of 3 and there are no blanks assigned in it; so index works as expected. When you define a length of 4 or more to b, the value used in index is right padded with blanks. So when length = 5, the string 'X=Y ' is what the index function sees. You can test this by adding an explicit blank in your first case. Index will not find the string. Or by using compress or trim functions on b within the index function in the second example.

LarryWorley · ‎08-15-2013

Are you sure your sorts worked? You can check your log to determine that. It does not make sense to me that if you sorted the table A by ACC_ID that record number 2400 should have a missing value, unless the 2399 record before it had missing values. I reach this conclusion because (at least on ASCII machines), missing values are always the smallest possible value, regardless of whether it is a string or numeric variable. I am real rusty on EBCDIC so if you are on a Z/OS machine I could be wrong. If the sorts did work and you have 2399 missing values in each file, then the compare is pointing to a difference in your files. Can you post some of the output from proc compare?

LarryWorley · ‎08-15-2013

I like to use ID variables for this. If you have a set of columns which uniquely identifies each record in both data sets, then ID statement lets proc compare handle the missing records appropriately. So for example with two datasets with unique personids on each record and where the second data set contains 25 records not found on the first, this will workL proc compare data=one compare=two ; id personid ; run . Note this assumes both data sets are sorted by personid. Another option is to use a where clause on the one with more records, but you still have to worry about sort order. proc compare data=one compare=two (where = ( not new record condition)) ; run . HTH Larry

LarryWorley · ‎08-14-2013

The approach proposed by Greek, using Proc Transpose, is the most generic way of handling this. It will handle many conditions, such as the number of values and level of the values, that you will need to consider programmatically if using either data step or sql. Also it is very efficient -- does its work in memory. Larry

LarryWorley · ‎08-14-2013

Pawan, Check out this paper from Cynthia Zender, http://http://support.sas.com/resources/papers/proceedings11/266-2011.pdf She shows how to use the ODS tagattr style attribute to accomplish adding leading zeros into spreadsheets through ODS including the excelXP tagsets. HTH, Larry

LarryWorley · ‎08-12-2013

John, Consider either Astounding's solution or Data _null_'s if you can consolidate to one proc. You are having issues with embedding macro references within call execute. It can be done but is tricky. In the first example you posted, the macro compiler will try to replace &NN during the data _NULL_ step compliation and causes and error with compiling. In your second example, %sysfunc is a macro call which call data step functions to return a value. Again the macro function is called at compile time and can not find a value for _n_. . Astounding suggestion fixes that.

LarryWorley · ‎08-09-2013

You see some buttons inside each reply, which let you mark as correct or as helpful

LarryWorley · ‎08-09-2013

I would start with creating a lookup data set with the relationship of practice_type and type. Depending upon your requirements you might add effectiveness dates, an effectiveness flag, etc. Something like this for your data: practice_type_level type effectiveness_flag 'FQHC' 1 Yes 'CAH' 2 Yes 'RHC' 3 Yes 'Independent_Multi_Site' 4 Yes 'Independent_Single_Site' 5 Yes 'new practice type tbd' 7 No 'Else value' 9 Yes You can modify this table as needed. Your dynamic code can read this table, filtering on the value of the effectiveness_flag if needed. Then you have sevaral options within SAS to do this: 1. Use this dataset to create a format which is then used in datastep with assignment statement to assign a value to the new type variable. This might be the simplest given my interpretation of your requirements. 2. Use the dataset to create macro vaiable 'arrays' which are then fed to a generic macro. This is similar to Anca approach, but allows dynamic changes to made to the control table instead of being hard-coded in that example. 3. Use the dataset to drive a data _null_ step which uses call execute to generate program, 4. Use the dataset to drive a data _null_ step which writes code to a temporary file which is then %included in job. You can find references to all of those approaches online and in the SAS documentation. Let me know if you need more details. Hope this helps. Larry

LarryWorley · ‎07-25-2013

I see the same window as REEza in EG 4.3.0

LarryWorley · ‎07-24-2013

Reeza is correct in pointing your towards datastep, since SAS SQL does not support windowing sql functions. I believe there is an alternative sql formulation which will generate the same result as the partition by. You could try this: Select Staff Job_Role, max(Start_Date) as start_date From Data_Table group by staff having start_date = max(start_date) ; Or you could use select distinct and omit the having clause. You might be able to implement that in the query builder.

LarryWorley · ‎07-24-2013

Methinks you have discovered some artifacts left over from the ancient days when cards statement really meant physical cards with an lrecl of 80. Looks to me like the assignment of a value to _infile_ is limited to 80 character, but when done by reading input buffer, the limit is not applied. I would consider scanning a character variable with the string value in it, rather than trying to get the cards statement to work. Something like this would work and perhaps be a little more transparent to a maintenance program later on: %let string=0=Emergency Services, 1=Tele Call, 2=Cusstomet Answer, 3=TAB, 4=Ptt Mute Voice, 5=Com User Voice Control, 6=Com System User Call, 7=ABC, 8=Disc, 9=XYZ, 10=Exe News, 11=ABC Ext Source, 12=MNGFR Com and BFC MUYH, 13=Not Req; data test; string = "&string"; i_scan = 1 ; do until (scan(string,i_scan,",=") = '') ; var = scan(string,i_scan,",=") ; output ; put _n_= var= ; i_scan + 1 ; end ; run ;

LarryWorley · ‎07-22-2013

Fleroo, Mea Culpa. I was frustrated and vented in the wrong direction.

LarryWorley · ‎07-22-2013

I missed the use of colon that Reeza pointed out.

LarryWorley · ‎07-22-2013

Flerro, I find it hard to believe that you have not looked at the link to documentation i sent you. Also perhaps you need to review macro processing also and spend a little time understanding the datastep process in SAS. I show below how it might work in your problem. the DHMS function is a datastep function. It needs to be within a data or proc step, not in open code as you have it. so your datastep code should look something like this: hos_date = dhms("&curdate"d,0,0,0): DATA safety_hrs; INFILE getin missover firstobs=4; INPUT @2 STATION 4. @12 CONTR_ID $8. @27 CONTR_NAME $35. @81 PRIM_SVC_AREA_ID $6. @91 DRIVER_NBR $7. @102 DRIVER_NAME $35. @145 PCK_DELV_HRS 5.2 @161 SPOT_HRS 5.2 @177 LINEHAUL_HRS 5.2; if PCK_DELV_HRS = . then PCK_DELV_HRS = 0.00; if SPOT_HRS = . then SPOT_HRS = 0.00; if LINEHAUL_HRS = . then LINEHAUL_HRS = 0.00; hos_date = dhms("&curdate"d,0,0,0): run ; When using macro variable, as in most other languages which use macros, you have two-step process in parsing code. step 1. Macro processor (or pre-compiler) reads submitted code and determines if it needs to make substitutions and then makes substitutions if needed, It then submits the statement to data step compiler or procedure processor. Step 2. The data step compiler parses the code in two passes, compiles the code and executes. In the modified code above, the macro processor 1. simply passes the first 17 lines (data ....line_haul_rs = 0.0; .) to the data step compiler 2. substitutes the value of curdate and passes hos_date = dhms("7/22/2012"d,0,0,0): to data setp compiler. 3. passes run statement to compiler which then compiles and runs.

Online Status	Offline
Date Last Visited	‎03-21-2016 10:07 AM

Re: Troubleshooting case statement

Re: SET statement point option

Re: SET statement point option

Re: Concatenation of 2 fields (need to retain Leading Zeros)

Re: SAS window trick?

Re: SAS window trick?

Re: Suggestions On Difficult Import

Re: Can Libname statement read the datasets which are named in capital...

Re: Suggestions On Difficult Import

Re: Suggestions On Difficult Import

Add BATCH SUBMIT button to EG

How to UPDATE on unique key in MySQL using SAS EG

Re: SAS/Connect, libname engine, invisible tables

Re: Help with merging databases

Re: why cards stop reading values at some length

Re: Hi

Re: compare

Re: LENGTH Statement causes INDEX Function to return an incorrect resu...

Re: compare

Re: compare

Re: SAS Help

Re: Loading data into an Excel: Limitaitons from ODS Tagsets.ExcelXP

Re: call execute and concatenating

Re: Writing Data Driven Programs

Re: Writing Data Driven Programs

Re: Find Latest value using ROW_NUMBER OVER (PARTITION BY 'X' ORDER BY...

Re: Find Latest value using ROW_NUMBER OVER (PARTITION BY 'X' ORDER BY...

Re: why cards stop reading values at some length

Re: Date from SAS showing 1/1/1960

Re: Date from SAS showing 1/1/1960

Re: Date from SAS showing 1/1/1960