About OS2Rules

OS2Rules · ‎08-24-2011

data_null: I think the problem here is that I don't have the key for the last record or the relative record number. Basically, I need to find the key for the last record so I can use it the next time I read the file to pull any records that have been added - rather than reading the entire file.

OS2Rules · ‎08-24-2011

MikeZdeb: Just wondering how efficient this would be with > 14 million records ?

OS2Rules · ‎08-23-2011

Thanks to all for the help. I checked with the Oracle DBA (she's cute!) and found out the field I was looking at was defined as an index variable for the Oracle table. I used the SQL suggested by NN and the CPU time is down to nearly nothing! I'm assuming that Oracle provides the index variable to the SQL rather than reading the actual data from the table.

OS2Rules · ‎08-22-2011

Hi All: I need to get the record-id (var: seq_number) of the last record in a large Oracle table. I use: data work01 (keep=seq_number); set oracle.table end=eof; if eof then output; else delete; run; but this runs for 20 minutes (there are > 14 million rows and the records are huge) and used 9 minutes of CPU alone. Any ideas on how to do this faster? The Oracle table is not mine so I can't make any changes on that side. Thanks in advance.

OS2Rules · ‎08-18-2011

Siddhartha: I assumme that you are running this query agaist a mainframe DB2 database? I think you may have run into a processing limitation that has been put in place by the mainfram DBA's to prevent queries taking too long a slowing down the database. There are several possible solutions: 1) Call you friendly mainframe DBA and appeal to their good nature to change the processing limit, 2) See if you can run the query from an account that has more priority on the mainframe - often these accounts have different limits. 3) Run the query in smaller 'chunks' - such as weeks, and then combine the results, 4) Call your friendly mainframe DBA (again) as get the index variables for the database to optimize your query - changing (or adding/removing) just one variable from the query can make a huge difference to the CPU time. Your chances of 1) or 2) working are slim. I recommend trying to make the query as efficient as possible to reduce the CPU overhead. Check how many records are returned in a 1 day query and the CPU time and then multiply by the total number of days your are extracting as a (very) rough estimate of the total CPU time you need. Then you can check how many records are returned by the failed query with the full month. That can give you an idea of how far the query got before it failed and how much time the query will need. As you can see I've hit this wall too...

OS2Rules · ‎08-16-2011

Art: I don't know why you are so worried - I writ my code like tht all the tim. I've been in this business about 30 years, man and boy, and I have yet to meet anyone who can touch-type! I sent you a private reply with the results of the code you provided (for the record - it didn't work quite as expected). Thanks again for all the help - above and beyond.

OS2Rules · ‎08-12-2011

I just tried your code - it is very close, but I have both lower-case and upper-case variable names. It sorts the lower-case first and then the upper-case. This is better than the seeming random list I have now, but ... call me a perfectionist. Just FYI - I learned to hate dislike VIEWTABLE in SAS V7 (yes, I had one) where the VIEWTABLE would take 45 seconds (!!!) to open any table no matter whether it had 1 row or 1 million. Changed the default to FSVIEW and never looked back.

OS2Rules · ‎08-12-2011

data_null: Doesn't this iinset a 'soft' page break, ie: a printed string within the spreadsheet where the page break shoud be? I need a 'hard' page break so each BY group is printed on a seperate physical page.

OS2Rules · ‎08-12-2011

Cynthia: I think the fundimental problem is that I want Excel to something that it might no be able to do. I would like to have 1 "sheet" that contains a report split with a BY group. But - when the sheet is printed, each of the BY groups is to be printed on a new page. To compound the problem, this is actually only part of the ouput because there are additional sheets within the same Excel file created by other parts of the same program. Everything is working with the exception of the paging of this one "sheet" .... I am not familiar enough with Excel to know whether it can even do this. I am already in contact the Tech Support on a related issue so I'll start with them. Thanks again.

OS2Rules · ‎08-11-2011

Hi All: I am using PROC REPORT and ODS with the ExcelXP tagset (v1.85) to create a spreadsheet. I have a BY value in the PROC REPORT that I would like to generate a new printer page (not a new sheet) for each BY value, but it is not doing it for some reason - the pages are continuous when I do a "print preview" in the resuting spreadsheet. I have read discussion: http://communities.sas.com/message/49124#49124, and Cynthia mentions that using a BY should cause the resuting spreadsheet to page for each value of the BY variable. I'm thinking that it is a ExcelXP tagset option, but which? Here is "most" of the code.... ods tagsets.ExcelXP options(sheet_name="DECODE=&decode" orientation='landscape' papersize='LETTER' embedded_titles='yes' embedded_footnotes='no' ROW_REPEAT='7' FitToPage='yes' sheet_interval='None' zoom='90' Pages_FitWidth='1' Pages_FitHeight='999' print_footer_margin='0.25' ROW_HEIGHT_FUDGE='1' absolute_column_width='12,12,12,12,12,14,14,12,12,14,14,14'); proc report data=reptdata2 nowd split='*' missing style(REPORT)={font_face=Arial font_size=9pt} style(SUMMARY)={font_face=Arial font_size=9pt font_weight=bold}; where decode=:"&decode"; BY byline2 notsorted; column var1 var2 var3 --- more --- var14; define var1 / group noprint; define var2 / group noprint; define var3 / display 'ISIN' style(column)={just=l}; --- more --- define var14 / computed 'some title'; rbreak after / summarize; compute var14; if _break_= '_RBREAK_' then var14 = var13..sum - var12..sum; else net = .; endcomp; TITLE1 j=c 'title1'; TITLE2 j=c "title2"; TITLE3 j=c "title3"; TITLE4 j=c "FOR &rptdate"; title5 #byval(byline2); footnote '&RPage &P of &N'; RUN;

OS2Rules · ‎08-11-2011

Art: Thanks - I will look into it (I'm reading your SGF paper). I was really hoping there would be a "zero programming" solution ie: a parm or option to reorder the table variable list. See you at TASS next month.

OS2Rules · ‎08-11-2011

Thanks to all for you replies. I thought that this was going to be simple, something that I overlooked. A little background - the file in question is produced by a SQL that is run against a bunch of DB2 and VSAM tables on z/OS mainframe and then downloaded to our server. It has 450,00+ records and over 350 variables. This is only one of about 50+ files that I deal with. Since the files are so big I would rather not have to run a program to rebuild them. My real problem is ad-hoc requests for data when someone asks for all records with some specific value. Since these people are not technical the request is "all cash transaction in US dollars". So - i need to find the variables in the table that contain these fields and believe me, there is no naming standard. I was hoping I could see the variables in alphabeic order (such as in PROC CONTENTS) with the data (so I can see "sample" data). I could just scroll across and fiind the variable with the required data and then build a program to filter the data once I know which variables to use. I was just hoping there would be some way to re-organize the data in the table so the variables are in sequence, rather than in the order they were created (first referenced).

OS2Rules · ‎08-10-2011

data_null_: It may be easy, but not exactly what I was looking for. I'm using the FSVIEW command in "interactive mode" on a Windoze box - so I just enter: FSVIEW tablename on the command line to see the table contents. (I also don't know the names of the variable until after I view the table.) What I was looking for was something like: FSVIEW tablename option It is like "double clicking" on the table name in the EXPLORER pane to get a VIEWTABLE (which also has the varibles out of order). I have actually modified my regristy so that the doing this will give me a FSVIEW rather than the much hated VIEWTABLE.

OS2Rules · ‎08-10-2011

Hi All: Quick question - I have a data table with about a bajillion variables (I exagerate a bit).... I like to use FSVIEW to see the content but I would like to see the variables in some sort of order (alphabetical?) in the FSVIEW display. Is there a way to do this? A simple easy way??? Thanks in advance.

OS2Rules · ‎07-25-2011

Hi: We do this all the time (on z/OS mainframe) First, set the date as in: (the date passed to DB2 needs the double quotes): CALL SYMPUT('INT_DATE',"'"||PUT(sas_date,YYMMDDD10.)||"'"); Then use in the SQL as in: PROC SQL ; CREATE TABLE DB2Table AS SELECT * FROM CONNECTION TO DB2 (SELECT Var1, Var2, ...etc... FROM Table WHERE Table_Date >= &INT_DATE ); quit; run;

Online Status	Offline
Date Last Visited	‎03-15-2022 01:28 AM

Re: Starting with Graphs

Re: version 9.3 and 9.4 not supported in windows 8?

Re: New user here with a FIRST.var & LAST.var question

Re: Upcase Column headings

Re: Lollipop Chart in SAS

Re: Generate year from sas date (YYMMDDN8.)

Re: FATCA Reporting

Change E-Mail

Re: How to generate ASCII value of string or number or special charact...

Re: EXCELXP and Special Characters

Re: SAS 9.4 -- How to set workspace window size

Re: How to generate ASCII value of string or number or special charact...

Remove Duplicate Characters In A String

Re: SLA

Re: Read CSV file with embedded CRLF

Re: Reading the last record of a large table

Re: Reading the last record of a large table

Reading the last record of a large table

Reading the last record of a large table

Re: Running SAS query on DB2

Re: SAS Dataset Options

Re: SAS Dataset Options

ExcelXP Page Break Question

ExcelXP Page Break Question

ExcelXP Page Break Question

Re: SAS Dataset Options

Re: SAS Dataset Options

SAS Dataset Options

SAS Dataset Options

DB2 Date9 format To SAS Serial Date....