About GenDemo

GenDemo · ‎01-08-2017

I was about to ask the same question. My company is considering aquiring a SAS server - so they asked me why they should...and I can't really answer them. I've also found information on the topic very limited. However, it comes down to your needs and requirments. Having an existing MS SQL data warehouse with Excel, SAS & SSMS reading data off that, I don't quite see the benifits of having a dedicated SAS server? I am probably ignorant (but it is bliss is not?) Please enlighten me... GD

GenDemo · ‎01-02-2017

ah...the sheet statement with the range statement confuses the thing. Silly that it doesn't give you an error or a note on this. proc import datafile= "C:\temp\book3b.xlsx" out= Test_Data replace DBMS= XLSX; /* sheet= "Test Data";*/ getnames= yes; /* datarow=2;*/ range="Test Data$A2:0"; run;

GenDemo · ‎01-02-2017

*facepalm* sorry guys.... I did something idiotic....just after the calculation I reset the TAT variable to 0 if its missing... TAT = intck('minute', FIRST_APP_SUBMISSION_DATE, First_Action_Datetime); ind_2pm = (timepart(FIRST_APP_SUBMISSION_DATE) <='14:00:00't); if TAT < 0 then do; neg_TAT = TAT; TAT = 0; end; thnx again...

GenDemo · ‎01-02-2017

Nope, its still not working. It doesn't matter what I do, it keeps on taking the first row (the one I want to ignore) as the variable names. Log window gives no errors or notes. The range statement makes no difference. If I comment out the range stament with the datarow statement in, I can either get the variable names in as the first row, or exclude it. proc import datafile= "C:\temp\book3b.xlsx" out= Test_Data replace DBMS= XLSX; sheet= "Test Data"; getnames= yes; /* datarow=2;*/ range="Test Data$A2:0"; run; So I can go the log way around and import it like this with the variable names in the first row, and then somehow create a new dataset from this.... GD

GenDemo · ‎12-28-2016

Hi All I have an Excel file that I need to import from another team to SAS. Usually I would save it as a CSV and import using a data step with an infile statement. But for various reasons i don't want to do that for this particular excersice - I need to import the Excel file directly. Please see attached an example of the file structure. The first row is a heading that I want to skip, with the variable names in the second row and the data from there on. I looked on the forum and for some reason the code that people suggest to do this, gives me an error. When I try and run the code below proc import datafile= "C:\temp\book3b.xlsx" out= Test_Data replace DBMS= Excel; sheet= "Test Data"; /* startrow=3;*/ /* namerow=2;*/ getnames= yes; /* mixed=yes;*/ /* scantext=yes;*/ /* usedate=yes;*/ datarow=3; run; I get "ERROR 180-322: Statement is not valid or it is used out of proper order." on the 'datarow' line. 1) How can I ignore the first row? 2) is there a way i can tell the import procedure I want to exclude certain columns? The 'Comments' column has a high potential to give errors and inconsistancies, and as such i would just like to ignore it. Thnx GD

GenDemo · ‎12-16-2016

Hi Unfortunately, I can't really post the code due to risk of sensitive information or IP. No I don't have a retain function on TAT. I'll check again when I am back in the office on Monday. GD ##- Please type your reply above this line. Simple formatting, no attachments. -##

GenDemo · ‎12-15-2016

Hi All So this is a bit of a wierd occurance. I am used to the intck function returning a missing value if one of the two dates are missing. But in an excersice I'm busy with, it returns a zero. I can't understand why. I moked up a little dummy data set as well to test, and then it returns a missing value. TAT = intck('minute', FIRST_APP_SUBMISSION_DATE, First_Action_Datetime); both variables are datetime22.3 I'd really appreciate some insight. GD

GenDemo · ‎11-29-2016

Awesome, that solved my problem! Thnx guys! 😄

GenDemo · ‎11-28-2016

Hi All I have been coding in base SAS (henceforth just SAS for simplicity) for a number of year now, and only now trying out EG (v6.1) as I need to schedule jobs to run automatically. Hence I've been coding in base SAS, then putting that program in a EG project and scheduling it. Through this process I noticed that SAS & EG treat their error slightly differently, and I've just encountered a problem with variable names and label that is completely different. Also, if there are other hints or inputs anyone can provide relating to the idea that I code in SAS and then just feed the program into a EG project, I would appreciate it. Its a simple excersice, but the way it handles the variable names & lables seem to be different, and I would really appreciate some insight into this. The Data that I am suing is as follows. I want a table with indicator/dummy variables to show what applications has each of the borrower types. Application_ID BorrowerType 74739 Primary 202258 Primary 210707 Primary 211814 Guarantor 1 211814 Guarantor 2 211814 Primary 220859 Guarantor 1 224290 Guarantor 1 224290 Guarantor 2 224790 Primary 225202 Primary 253492 Guarantor 1 253492 Primary 287221 Co-Borrower 287221 Primary Hence I run proc freq data=BorrowerTypes noprint; tables Application_ID*BorrowerType / missing out=BorrowerTypes_freq (drop=PERCENT); run; Application_ID BorrowerType Frequency Count 747397 Primary 1 2022858 Primary 1 2109707 Primary 1 2118144 Guarantor 1 1 2118144 Guarantor 2 1 2118144 Primary 1 2208559 Guarantor 1 1 2208559 Primary 1 2247290 Guarantor 1 1 2247290 Guarantor 2 1 2247290 Primary 1 2252021 Primary 1 2253492 Guarantor 1 1 2253492 Primary 1 2287221 Co-Borrower 1 2287221 Primary 1 Then I run a transpose: proc transpose data=BorrowerTypes_freq out=BorrowerTypes_freq_t (drop= _LABEL_ _NAME_); by Application_ID; var count; ID BorrowerType; run; Application_ID Primary Guarantor_1 Guarantor_2 Co_Borrower 747397 1 . . . 2022858 1 . . . 2109707 1 . . . 2118144 1 1 1 . 2208559 1 1 . . 2247290 1 1 1 . 2252021 1 . . . 2253492 1 1 . . 2287221 1 . . 1 everything up to here runs the same in SAS & EG. Then in the next step is where the difference comes in: data BorrowerTypes_freq_t; set BorrowerTypes_freq_t; Guarantor = (Guarantor_1 = 1 | Guarantor_2 = 1); Secondary = (Guarantor = 1 | Co_Borrower = 1); run; The results in SAS look like this: Application_ID Primary Guarantor_1 Guarantor_2 Co_Borrower Guarantor Secondary 747397 1 . . . 0 0 2022858 1 . . . 0 0 2109707 1 . . . 0 0 2118144 1 1 1 . 1 1 2208559 1 1 . . 1 1 2247290 1 1 1 . 1 1 2252021 1 . . . 0 0 2253492 1 1 . . 1 1 2287221 1 . . 1 0 1 while in EG they look like this: Application_ID Primary Guarantor 1 Guarantor 2 Co-Borrower Guarantor Secondary Guarantor_1 Guarantor_2 Co_Borrower 747397 1 . . . . . . . . 2022858 1 . . . . . . . . 2109707 1 . . . . . . . . 2118144 1 1 1 . . . . . . 2208559 1 1 . . . . . . . 2247290 1 1 1 . . . . . . 2252021 1 . . . . . . . . 2253492 1 1 . . . . . . . 2287221 1 . . 1 . . . . . I hope this makes sense...and i would appreciate any insight into it - SAS can't use the spaces in the variable names, and hence puts "_" in, however, it seems that EG can use this, and hence my base SAS code does not work. Thnx in advance GD

GenDemo · ‎10-27-2016

Hi, I have a similar problem. I just moved to a new company where we don't have a scratch DB on the SQl server. To surcumvent this, I used to upload my table with the primary key to the scratch DB; then connect to the DB and execute SQL on the server to join my table; then pull it to my local machine. However, as I mentioned, we don't have a scratch DB here. I tried the above example, but it still gives me an error...i hope you can help: /*example*/ data tableA (drop= i); do i = 1 to 10; sys_ID = i; output; end; run; proc sql; connect to odbc as imaging (dsn=imaging); create table tableB as select * from connection to imaging (select * from work.tableA as a left join imaging.dbo.AdviceSheetAdditionalRequirements b on a.sys_ID = b.sys_ID ) order by a.sys_ID ; disconnect from odbc; quit; The Log window says: ERROR: CLI describe error: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'work.tableA'. : [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

GenDemo · ‎10-20-2016

Ah Thank you This basically explains it: Note: In a WHERE statement or clause, the <> operator is equivalent to NE.

GenDemo · ‎10-19-2016

Hi All I am wondering if anyone knows why I sometimes get the following note when using the "<>" ('unequal') operator in a data step procedure: NOTE: The "<>" operator is interpreted as "MAX". Sometimes this happens in a proc sql as well...

GenDemo · ‎11-03-2015

I have the same problem with indicator variables that I build, where I spesify the format as "1." and then the proc means gives me issues. What you can do, though I do not know of any consequences of this: proc means data= a; format _all_; informat _all_; class xxx; var yyy; output out=b sum=yyy; run;

GenDemo · ‎10-19-2015

WOW! awesome function, thnx!

GenDemo · ‎06-03-2015

Thank you very much. Your solution is not exactly what I was hoping for, but it is much shorter than what I have. I appreciate your help. Just for interest, what I did: data want2 (drop= time_str time_str2); set have; /*Application Datetime*/ format createdate2 date9. createtime2 time8. creationdatetime datetime22.3; createdate2 = input(put(createdate,12.),b8601da.); time_str= compress(input(put(createtime,12.),$12.)); if length(time_str) = 5 then do; time_str2 = compress("0"||substr(time_str,1,1) ||":"|| substr(time_str,2,2) ||":"|| substr(time_str,4,2)); createtime2 = input(put(time_str2,$8.),time8.); end; else do; time_str2 = compress(substr(time_str,1,2) ||":"|| substr(time_str,3,2) ||":"|| substr(time_str,5,2)); createtime2 = input(put(time_str2,$8.),time8.); end; creationdatetime = input(compress(put(createdate2, date9.)||":"||put(createtime2, time.)),datetime22.3); run;

Online Status	Offline
Date Last Visited	‎08-13-2021 03:35 AM

Re: Discord/Slack community

Re: Discord/Slack community

Discord/Slack community

Re: Data processing the "SAS way" in Python

Data processing the "SAS way" in Python

Re: Getting my program files back for SAS University Edition

Re: Getting my program files back for SAS University Edition

Re: Getting my program files back for SAS University Edition

Re: Getting my program files back for SAS University Edition

Re: Getting my program files back for SAS University Edition

Re: Discord/Slack community

Re: Discord/Slack community

Re: Data processing the "SAS way" in Python

Re: Data processing the "SAS way" in Python

Re: Sas connection to powerbi

Re: Proc Import Excel File with data in second row

Re: Sas connection to powerbi

Re: Sas connection to powerbi

Re: Benefits of SAS server

Re: Proc Import Excel File with data in second row

Re: intck function not giving a blank

Re: Proc Import Excel File with data in second row

Proc Import Excel File with data in second row

Re: intck function not giving a blank

intck function not giving a blank

Re: variable name/label issue - running base SAS script in EG

variable name/label issue - running base SAS script in EG

Re: SAS accessing large table names on Amazon redshift

Re: The "<>" operator is interpreted as "MAX".

The "<>" operator is interpreted as "MAX".

Re: Help for a new SAS user- ERROR: Asterisks are an indication of a f...

Re: Function to remove leading zeros

Re: Converting numeric date and time to SAS datetime22.3

SAS Analytics Explorers