About ncsthbell

ncsthbell · ‎06-30-2015

I am creating a report using Proc Report and exporting using ODS TAGSETS.EXCELXP. For several columns I want to print the header but only the data value in the column on the first occurrence of the value. Example: If my columns are 'COUNTY' & 'TOWNSHIP' and there are multiple rows, I want to print the value of 'COUNTY' on the first occurrence of the multiple rows and do the same for 'TOWNSHIP'. I tried using 'ORDER' as an option in the PROC Report section on the 'Define' statement for the columns but it does not seem to work. I have read through the options for the ODS Tagsets for excel and cannot find anything that looks like what I am trying to do. I want report to look like this: COUNTY Township Columbus Middle Creek Willow Spring Flat Rock Verona Verona Island Hampton I would appreciate any suggestions! I am not an expert with SAS Reporting

ncsthbell · ‎10-04-2013

Hello, I have a request to create a report that is basically a statistical report. The report is to show counts of 'how many' observations there are for particular columns based on the value of the column. For example: data column 'TType', this column can have up to 5 different distinct values, values (1,2,3,4, &5). I need to get a count for how many observations there are in the table for each value. I have done this by using a proc freq. I also have to do counts on 20 other columns based on some criteria, for example, count the number of observations where a particular column is not null. I am not sure the direction to go with for this. I thought there would be a better way to code this than having 20 separate 'select count' statments. I also need to all the counts to a data set. This report has requests for the averages on some columns as well as counts for some columns. Would appreciate any suggestions! Thanks

ncsthbell · ‎03-28-2013

Ursula, you are correct & this works. However, my user has decided they want this job scheduled as a batch job in SAS so now it will not be running on the Portal giving the user the option to 'open' or 'save' the output .xml file. I just edited my post with the info about running it as batch.

ncsthbell · ‎03-28-2013

I am on SAS 9.2 and have written a program that uses ODS Tagset.exelxp and creates 4 separate worksheets contained within one excel spreadsheet. I am use 'Proc Report' to create the multiple worksheets. When run from the SAS Portal, the output is in '.xml' format which when opened looks like an excel spreadsheet with 4 worksheets(tabs). This is working correctly. However, the user does not want to run it from the portal so I am having to create it as a SAS batch job to be scheduled. I can't quite figure out how to change the output from going to an 'xml.' file and have the output go to a server file/path location and allow it to be opened as an excel spreadsheet with the multiple worksheets. I have tried using the Export function but Excel is not an option in the output types. Any recommendations on how I can accomplish this? Thanks

ncsthbell · ‎01-16-2013

I am creating a stored process from progam code. I originally had some variables defined using '%LET' that were for testing purposes. These have been commented out in my code (p_fromdate, p_todate) and are still showing up in the list of prompts available from the SAS code. I also have a new prompt that is not showing up in the list. Can you tell me how to get the new prompt to show up in the list of prompts from my code?

ncsthbell · ‎12-19-2012

SandorSzalma, I am not clear on this: If you have all data in one database, your Oracle user can get select grants to the objects in the two other schemas. (--select 'GRANT SELECT ON SCHEMA_1.' || table_name || ' TO YOUR_ID;' from all_tables where owner='SCHEMA_1'; ) you can use your user in the libname statements: (same like from Hai.Kuo) libname schema_my oracle path=db schema=my authdomain=authmy; libname schema_1 oracle path=db schema=one authdomain=authmy; libname schema_2 oracle path=db schema=two authdomain=authmy; Are you saying that I need to put the 'select grant' statement in my code? What I am writing will be going into our production environment and they will not permit my userID to be in the code. Also, as Doc's reply stated above, SAS will pull in both schemas to do the joins. This has affected the performance so this may not be the optimal method for me to use.

ncsthbell · ‎12-19-2012

Doc, thanks for your reply. I noticed that when I coded in the second schema that the performance slowed down tremendously so what you stated makes sense. SAS must have pulled in the data to do the joins of the 2 schemas. I have asked our dba's to add the 1 table I need from the one schema into the other schema and they will not do it so I am stuck on trying to figure out how to handle this in the best way without performance being affected. I am dealing with very large data. I tried using a 'connect' to the first Oracle schema and it was quick. Then I created a SAS dataset for the other oracle table in the other schema. I had a hard time getting it to work as I am new to this and do not really understand how the connects & libname things work together. I wound up removing the 'connect' to oracle and I just put a libname statement in for the SAS dataset and it work, but again, it was slow. Any recommendations on how to accomplish this in the most efficiend method? Thanks

ncsthbell · ‎12-17-2012

I am writing a SAS program and using proc sql to select the data from an Oracle table. I have a 'connect' statement defined with the userid & password assigned to the the Oracle schema and the path the tables reside in for SAS. connect to oracle(USER=U100_DEVT PASSWORD="{SAS002}7A179F4D2CBCC75407LIS87023333D8144387AEC4A20A7DC4DEA5C6152" path=devt_tbls); I have 2 issues: 1) I need to read in data from 2 different Oracle schemas each having their own userid/passwords. Whenever I added a second connect statement to my code, I got an error saying that the Oracle connection had already been made. How can I access the Oracle data from 2 different schemas? 2) I need to read data in an Oracle schema AND a SAS dataset. I put in the 'connect' for the Oracle schema(tables) and then used the LIBNAME statement for my SAS dataset. I get an error saying my SAS dataset can not be found. It seems to think the SAS dataset is an Oracle table because of the connect statement. This is the first program I have written in SAS and I am struggling with how to make these connections! Any suggestions would be appreciated! Thanks

ncsthbell · ‎12-10-2012

Cynthia, thank you for your reply! I really appreciate you taking the time to explain the different options for usage & display, etc. Your information was a GREAT help!! I have changed my progam code and the date is now formatted correctly. Thanks again! Great start for a Monday for me!

ncsthbell · ‎12-07-2012

Thank you for your reply. My sample data in the column 'orig_update_dt' is 25JUL2000:00:00:00 I put the options on the proc report you suggested 'nowd out=temp'. What I see in results in 'temp' is the display of the data results in column format. I am using the 'display' option on both the original data coming in the column 'orig_update_dt' and the new computed column 'new_update_dt'. This seems so simplistic. In the SAS Training manual "SAS Report Writing 1: Using Procedures and ODS" on page 4-73 there is a section called 'Hiding Report Columns'. This is what is says: To hide the variables that are used to calculate the new column value, use the NOPRINT option in the DEFINE for each variable you want to hide." Since this is referring to 'variables', I assume this means of any data type including a date datatype. This column is defined in the input as 'DATETIME20." This is exactly what I am doing. I am using the variable 'orig_update_dt' to compute the formatted date into the variable 'new_update_dt' and then using 'NOPRINT' instead of 'DISPLAY' on the variable 'orig_update_dt'. When I change DISPLAY to NOPRINT to hide it, the date will not compute in the 'new_update_dt' variable. However, if I leave DISPLAY instead of NOPRINT, the variable 'new_update_dt' is computed correctly.

ncsthbell · ‎12-06-2012

I am attempting to compute a new date variable to format the original date variable into a different format. The value of the 'orig_update_dt' column is 25JUL2000:00:00:00. I get the correct results for both columns 'orig_update_dt' & 'new_update_dt' as long as I use 'display' on the DEFINE statement. The results are: 'orig_update_dt' = 25JUL2000:00:00:00 'new_update_dt' = 07/25/2000 However, if I change the DEFINE statement on 'orig_update_dt' and use 'noprint' instead of 'display', the computed variable 'new_update_dt' does not compute. The value of 'new_update_dt is 01/01/1960 when the 'display' is changed to 'noprint'. I know this is the SAS base date so I guess it means it is not computing the new variable for some reason. I am pretty new to this so am I missing something? my code: COLUMN orig_update_dt new_update_dt DEFINE orig_update_dt / display ; DEFINE new_update_dt / display computed format=mmddyys10.; <= if COMPUTE new_update_dt; new_update_dt=(datepart(orig_update_dt)); ENDCOMP;

ncsthbell · ‎05-04-2012

Thank you Linlin. I have not used SAS EG (4.2) to create my own code yet. I am using query builder (just learning EG). Do I need to create a 'program' and put the code in it to run?

ncsthbell · ‎05-04-2012

Thanks RickM!!

ncsthbell · ‎05-03-2012

Looks like some formatting has been changed since last time I used the forum. Can you tell me where I go now to look at a list of all discussions I have created? Also, why is my name being displayed instead of my userID as previously?

ncsthbell · ‎05-03-2012

I am using SAS EG 4.2 and pretty new to it. I am trying to use a query builder to join together 2 tables where the key can exist in one or more columns. Example: Data: TableA - col1 <-- if TableA.col1 value is in either one of the columns on TableB, I want to join the data, can't figure out how to accomplish this. TableB - col1, col2 I need to join the TableA to TableB if TableA.col1 = (TableB.col1 or TableB.col2) I tried to build the join for TableA.col1 = TableB.col1, then I went into 'Edit' on the join properties and added 'OR TableA.col1 - TableB.col2) I get syntax errors because it seems to be expecting the operand to be 'AND', so what I wound up with wad: TableA.col1 = TableB.col1 AND OR TableA.col1 = TableB.col2 Any suggestions would be greatly appreciated!!! Thanks

Online Status	Offline
Date Last Visited	‎04-11-2016 09:58 AM

Re: Why am I getting error when using function 'length' to test a vari...

Why am I getting error when using function 'length' to test a variable...

Re: Need guidance for best way to capture user entered prompts and cre...

Re: Need guidance for best way to capture user entered prompts and cre...

Re: Need guidance for best way to capture user entered prompts and cre...

Need guidance for best way to capture user entered prompts and create ...

Re: When stored process using prompts runs on SAS portal can a message...

When stored process using prompts runs on SAS portal can a message be ...

Re: Can not get macro variable to resolve to correct value.

Can not get macro variable to resolve to correct value.

Re: General questions about using forum.

Using EG 4.2 - how to extract 1st 3 digits from numeric column?

Prompt not coming up when running query builder

Re: Trying to format column Proc Report using ODS TAGSETS.EXCELXP to c...

ODS Excel - Print value of column once when multiple rows have same va...

Need recommendations for best solution to create report with multiple ...

Re: How to send ODS tagset.exelxp output to external file location?

How to send ODS tagset.exelxp output to external file location?

When creating stored process prompts from 'Prompt from SAS Code', the ...

Re: How to access Oracle data residing in separate schemas in SAS?

Re: How to access Oracle data residing in separate schemas in SAS?

How to access Oracle data residing in separate schemas in SAS?

Re: How to hide date variable used to calculate new column in Proc Rep...

Re: How to hide date variable used to calculate new column in Proc Rep...

How to hide date variable used to calculate new column in Proc Report?

Re: How to join 2 tables using 'or' condition on key fields?

Re: General questions about using forum.

General questions about using forum.

How to join 2 tables using 'or' condition on key fields?