About LarryWorley

LarryWorley · ‎12-18-2013

Or are you trying to get something like this: ls_step_cd number_of_occurences M17 5 K16 10 and so on If so restructure your sql as create table step_count as select ls_step_cd, count(ln_no) as number_of_occurances from lm_steps where ls_actual_compl_dt between &pmb and &pme and ls_step_cd in ('M17','K16','L39','L96','M60','L58','L44','L52') group by ls_step_cd order by ls_step_cd; Note it always help to tell what you are trying to do. Syntactically, the 'end' key word closes a case statement. So after the SQL engine found 'as M17' it as looking for what can come next legally. For example, a new field in the select statement ==> ',' or column modifier like FORMAT or LENGTH, etc. HTH

LarryWorley · ‎09-20-2013

Robert, In terms of documentation, I keep pdf copies of several SAS manuals on my PC. So that is the first place i go. In this case, SAS 9.2 Language Reference: Dictionary, 4th Ediition, pp 1764 to 1774. Online you can get to this from http://support.sas.com/documentation/ The default format there is HTML but you can switch to PDF if you want and then download. Or you can use this search string in gooble or bing, "set statement option nobs site:support.sas.com" I initially thought of sql or hashing but some other things might also work; a. Using set option key=. This also allows direct access but does by value rather than record number. You would not have to read every record in the second data set for each iteration of the datastep b data step merge with by statement on id in and out. This miight reduce the comparisons being made greatly. I can't say for sure but the original code seems to be getting from latest in value and first out value. This would require both data sets to be sorted or indexed appropriately. But then I think one pass through both data sets is all that is needed. c. Make a single pass through second dataset to construct unique records for each ID with appropriate in, out, in_dept and out_dept. Seems that can be done independently of the information in first data set. Then merge constructed dataset with first dataset. HTH Larry

LarryWorley · ‎09-20-2013

Robert, From a non-technical standpoint, the data step reads a record from x, then reads all records in the yy, conditionally assigning values to dept_in and dept_out, and then outputs a record. It then reads the next record in x, and so on until all of x is read and output. The nobs=nobs set option sets up a vaiable called nobs which is known to SAS but not in the output dataset. This variable is assigned a value before any daata is read.This information is stored in the header information for a sas data set. So at compile step, SAS reads that value from data set header and then can use for controlling looping. The do i = 1 to nobs; causes the variables i and nobs to be declared. The nobs= in set statement causes actual number of observations to be saved as the variable nobs. Point=i allows direct access to specific records in a data set and only reads that record. There may be more efficient ways of obtaining the same results and I would consider revising if either of the data sets is large. HTH Larry

LarryWorley · ‎09-20-2013

I see several issues. Variable OUT_REC is mis-spelled in the if statement. So branch is never executed. IF SCAN(OUTREC,2,':') = "####" THEN 2. After correcting mis-spelling the branch is still not executed. IF SCAN(OUT_REC,2,':') = "####" THEN 3. Investigation of value returned from scan is ' ####'; notice the leading blank. 4. Change if statement to following and this will work: IF compress(SCAN(OUT_REC,2,':')) = "####" THEN Some comments 1. I discovered #1 by looking at the log. It showed that OUTREC was uninitialized, which frequently points to mis-spelling 2. #3 took a little more effort to find that there is a leading blank in the string returned from scan. a. First I noticed code was different bewteen the two lines but was getting the same results. So I said, "Aha, the first branch may not be exercised. b. I modified your code to have "then do; .... ;end;" and inserted a put statement to show me which branch I was in. c. That verified that only the else branch was executed. d. I then put the string returned from first scan and saw it had a leading blank. 3. Posting relevant portions of the log is almost always helpful in solving these kinds of issues. It will let many of the folks on this website quickly see what is happening. I would have definitely been able to get further in diagnosing the problem without modifying your code to run for me. 4. I would consider a different configuring the format selection -- perhaps just look at the first word in OUT_rec (Total or Output, in your example). Using the repeated # sign is cryptic and also difficult to verify correct configuration. But maybe you can't change this. HTH Larry

LarryWorley · ‎08-22-2013

windows >> resize gets you close. I do not see a buil-in hot key for this.

LarryWorley · ‎08-22-2013

Maybe windows >> tile vertically or shift+f4 ?

LarryWorley · ‎08-21-2013

Glad you are making some progress. You might try looking at the data in the original dataset. The import wizard is somewhat of a black box. It creates a text file behind the screen and then reads that text file. You see the code for reading the text file, but not for creating it, It might be doing something you don't expect, so you may be checking the F! variable for a particular string which does not translated as expected by the black box. lw:-)

LarryWorley · ‎08-21-2013

Good question. In AIX 9.2, this behaves very weirdly. I tested with three files -- test.sas7bdat, Test.sas7bdat and aTest.sas7bdat. I used Unix 'cp' command to create the two non standard files. I think best advice is to not muck with dataset names and let SAS handle moving from system to system if possible. Here is log: 322 data test; 323 set sasuser.'aTest'n ; ERROR: File SASUSER.ATEST.DATA does not exist. 324 run ; NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set WORK.TEST may be incomplete. When this step was stopped there were 0 observations and 0 variables. WARNING: Data set WORK.TEST was not replaced because this step was stopped. NOTE: DATA statement used (Total process time): real time 0.02 seconds cpu time 0.00 seconds 　 325 data test; 326 set sasuser.'Test'n ; 327 run ; NOTE: There were 49473 observations read from the data set SASUSER.TEST. NOTE: The data set WORK.TEST has 49473 observations and 32 variables. NOTE: DATA statement used (Total process time): real time 0.12 seconds cpu time 0.05 seconds

LarryWorley · ‎08-21-2013

DZ, Okay, I thought that might be how the data looked. I have not been able to re-generate that but can now offer a couple of methods: 1. Going back to the range names I mentioned earlier. Define three ranges which refer to the data for the three questions you want to look. Yes this involves modifications to the excel file, but it significantly reduces the coding you need to do. Using the import wizard, In tab 1 of 2 set a distinct dataset name. Then on Tab 2 of 4 you need to select predefined named range and legt SAS rename columns to comply with SAS naming conventions. Try that to see if it works. 2. I can provide some example code to read the data as you seem to have it, but it is going to be complex. Something like this might work to identify the survey69440225 items: data survey69440225 ; set your_input_data ; rename f1 = operator f2 = item2 f3 = item10 f4 = item2; . . f11 = item9 ; if f1 contains '69440225' then do until upcase(f1) = 'TOTAL' ; set your_input_data ; if upcase(f1) not in ('TOTAL','OPERATOR') then output ; else stop ; end; run ; This basically cycles through your data until it finds the header for desired table. It then skips the column headers record, reads each of the data records and outputs to dataset with new names. And then stops when entire table has been read. Treat #2 as pseudo code. It is untested, but the logic should hold. HTH Larry

LarryWorley · ‎08-21-2013

DZ, Okay, so you are using the import wizard in EG to read the spreadsheet into a dataset. It still would be helpful for us to understand how that dataset is structured -- column names and types and some sample records in the dataset. I can think of several ways the dataset could look that the import wizard is creating and don't want to propose a solution to an incorrectly assumed case. So please post either proc contents and subset proc print or simply a subset of the data set which can be attached. thx, lw:-)

LarryWorley · ‎08-21-2013

Following up on Reeza's suggestion, you can either write the table directly to the access database if you have Access to PC Files installed. Or you can write to an excel or csv file and have MS/Access read that file.

LarryWorley · ‎08-21-2013

DZ, How are you doing this? proc import from excel, proc import from csv, libname statement, etc. Also can you provide a sample of what the data looks like after you have imported into a SAS data set? Don't need everything -- proc contents and proc print of 20 obs or so would be sufficent. It would be helpful to see how the data is organized. lw:-)

LarryWorley · ‎08-21-2013

Splitting hairs here , Tom;-) But lowercase dataset file names is a SAS on Unix requirement, not Unix requirement.

LarryWorley · ‎08-21-2013

I believe all sas data sets in unix flavors should be completely lower case. So i wonder how you have upper case names or if these are really sas data sets? It is possible if the datasets were copied outside of SAS that Upper case names could happen. If that is so, I think you may need to rename at the Unix level, like this "mv OLD.sas7bdat old.sas7bdat" Can you provide an example of the full name of one that is not being copied by proc copy?

LarryWorley · ‎08-21-2013

D.Z., Can you source the data which was the input to this spreadsheet? If so, look at using that, rather than the spreadsheet, which is obviously a report. If not then do you have Access to PC files? If so, then an option would be to create range names for each of the presentation tables in the spreadsheet. Finally if you do not have Access to PC Files, you might try the following: 1. Understand the organiztion of the tables in your spreadsheet. It looks like there is some regularity which might guide your processing. By that I mean it looks like there are different tables which have the same column format and number of rows. Detemine how you can programmatically recognize the different styles. 2. Save the file as a csv file. 3. Simplest but brute force approach would be to build multiple datasteps which read one table at a time (you could use first obs and last obs dataset options for this). Then combine the tables as needed. 4. A more elegant solution might be to read the entire file but using different input statements for the different tables and output to a different dataset depending upon the table. I would be tempted to start with my first approach. If this is to be repeatable, then I would look at the more programatic approach in order to make more roboust. HTH, Larry

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: 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

Re: Multiple selection using proc sql

Re: Suggestions On Difficult Import

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

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

Re: Suggestions On Difficult Import