03-10-2021
Sas_Act_114
Fluorite | Level 6
Member since
07-20-2018
- 15 Posts
- 4 Likes Given
- 0 Solutions
- 0 Likes Received
-
Latest posts by Sas_Act_114
Subject Views Posted 1003 03-02-2021 02:01 PM 3454 03-17-2020 05:35 PM 3467 03-17-2020 05:22 PM 3518 03-17-2020 03:20 PM 2148 02-20-2020 12:56 PM 2269 02-19-2020 03:04 PM 2289 02-19-2020 02:51 PM 3529 02-13-2020 02:49 PM 3538 02-13-2020 02:24 PM 3558 02-13-2020 01:50 PM -
Activity Feed for Sas_Act_114
- Posted Splitting one row with two dates into multiple rows by month on SAS Programming. 03-02-2021 02:01 PM
- Posted Re: Seeking information on compatibility of 64-bit Excel and 32-bit PC SAS on SAS Programming. 03-17-2020 05:35 PM
- Posted Re: Seeking information on compatibility of 64-bit Excel and 32-bit PC SAS on SAS Programming. 03-17-2020 05:22 PM
- Posted Seeking information on compatibility of 64-bit Excel and 32-bit PC SAS on SAS Programming. 03-17-2020 03:20 PM
- Posted Re: PROQ SQL - Left joining on a sas dataset to a table pulled from a teradata server on SAS Programming. 02-20-2020 12:56 PM
- Liked Re: PROQ SQL - Left joining on a sas dataset to a table pulled from a teradata server for Patrick. 02-20-2020 12:56 PM
- Posted Re: PROQ SQL - Left joining on a sas dataset to a table pulled from a teradata server on SAS Programming. 02-19-2020 03:04 PM
- Posted PROQ SQL - Left joining on a sas dataset to a table pulled from a teradata server on SAS Programming. 02-19-2020 02:51 PM
- Posted Re: Excluding rows when two variables have specific values on the same row with PROC SQL on SAS Programming. 02-13-2020 02:49 PM
- Liked Re: Excluding rows when two variables have specific values on the same row with PROC SQL for ErikLund_Jensen. 02-13-2020 02:49 PM
- Posted Re: Excluding rows when two variables have specific values on the same row with PROC SQL on SAS Programming. 02-13-2020 02:24 PM
- Posted Excluding rows when two variables have specific values on the same row with PROC SQL on SAS Programming. 02-13-2020 01:50 PM
- Posted Need help understanding left join logic (in proc sql) on multiple tables on SAS Programming. 12-19-2019 06:32 PM
- Posted Re: Capturing the first and last date record into new tables on SAS Programming. 05-08-2019 04:13 PM
- Liked Re: Capturing the first and last date record into new tables for PaigeMiller. 05-08-2019 04:10 PM
- Posted Capturing the first and last date record into new tables on SAS Programming. 05-08-2019 03:49 PM
- Liked Re: Converting Teradata Pulled Date Data to YYYYMMDD Format for r_behata. 12-05-2018 03:02 PM
- Posted Re: Converting Teradata Pulled Date Data to YYYYMMDD Format on SAS Programming. 12-05-2018 02:47 PM
- Posted Converting Teradata Pulled Date Data to YYYYMMDD Format on SAS Programming. 12-05-2018 02:05 PM
-
Posts I Liked
Subject Likes Author Latest Post 1 2 1 1
03-02-2021
02:01 PM
Hello, I have a table with two date variables that make a date range (as an example, Var1 is 01/01/2019 and Var2 is 06/30/2019). I want to take that rows and split it into multiple rows, with a row for each month within the date range. Thus in the example I have, the one record would be split into six records (201901, 201902, 201903, etc..). I know this is somehow possible with the INTCK and INTNX function and using some kind of loop, but I can not figure out how to use them properly to get the results I want. Any help would be appreciated, thank you!
... View more
03-17-2020
05:35 PM
So I did do just that and just got off the phone with them. You were correct Tom in that using the PC File Server would be the way to work with the two systems of different bit versions. He made an important point too though, that with Microsoft 365, the program is automatically updated (since the actually software is in a cloud) and when that happens, there can be issues between it and SAS, to where the SAS development team needs to correct it, causing downtime. The recommendation was to keep the same bit version if possible, which is what I will push for.
... View more
03-17-2020
05:22 PM
So I looked up information on the PC Files Server, however I think this would apply if I had 64-bit SAS and was using Microsoft 32-bit. This is what I found from the SAS support website: SAS PC Files Server enables: 64-bit SAS on Windows to access PC files when 32-bit Microsoft Office® is present. SAS on Linux or UNIX to access PC files stored on a Windows server. However my situation is 32-bit SAS and 64-bit Microsoft Office, which I don't know if this will resolve. The marco enabled files (XLSM) can't be gotten rid of, they are a part of our process.
... View more
03-17-2020
03:20 PM
Currently at my job, I use PC-SAS 9.4, 32-bit and Microsoft Excel 2010, 32-bit. The company is considering moving to Microsoft 365, which is a 64-bit version. This could be problematic, as in my job, importing and exporting between Excel and SAS is done a lot, with bringing in information from multiple tabs in excel files to manipulate data from another source (database), to then export to multiple workbooks. I am, however, extremely unfamiliar with the whole 32-bit and 64-bit concept and am looking for information on compatibility issues that could occur between 32-bit SAS and 64-bit Microsoft Office/Excel. In particular, how would it affect using the PROC IMPORT and PROC EXPORT statements between the two, given the excel files are .xlsx and .xlsm (workbook and macro-enabled) formats. Any information in appreciated, thank you.
... View more
02-20-2020
12:56 PM
This is what I was looking for, thank you very much!
... View more
02-19-2020
03:04 PM
The issue with this, is I can't just do a pull on the table from the Teradata server. This table is massive, I'm talking several 100 millions of rows of data and the pull times out due to how long it takes. Otherwise yes, your approach is exactly what I would be attempting
... View more
02-19-2020
02:51 PM
Hello all, I'll try to explain this as best as possible. I have a SAS Dataset with just one column, that has a list of about 3 to 4 million unique IDs. On the teradata server, there is a table whose data I want to pull for all the IDs in the SAS dataset. The issue I am encountering is that when I use PROC SQL, it does not recognize or seem to know about the SAS Dataset, thus I can't do a left (or right) join on the pull. I also can not do a pull on just the table from the teradata server as it is extremely large and the only condition I have to limit the pull are these IDs. Here is an edited sample of the program I am running in what I am trying to do, with the SAS Dataset named SAS_TABLE and the teradata table named TERA_TABLE: PROC SQL;
CONNECT TO ODBC(DATAsrc='SERVER NAME');
CREATE TABLE PULL1 AS
SELECT *
FROM CONNECTION TO ODBC
(
SELECT *
FROM SERVER.TERA_TABLE B
RIGHT JOIN SAS_TABLE A
ON A.ID = B.ID
);
QUIT; I've tried it this way and also a left join version, but each time, I get an error stating the SAS_TABLE does not exist: ERROR: CLI prepare error: [Teradata][ODBC Teradata Driver][Teradata Database] Object 'SAS_TABLE' does not exist I am assuming what the program is trying to do is find "SAS_TABLE" in the actual database, which is what is causing this. Any help on this would really be appreciated, thanks!
... View more
02-13-2020
02:49 PM
This was the command I needed, worked perfectly, thank you.
... View more
02-13-2020
02:24 PM
I apologize if I wasn't clear enough in the original post. There is no joins, I am simply pulling the data from one table, which even that I do not believe to be relevant. For all intensive purposes, this just involves one table (or dataset if you will) with several variables/columns in the one table. As an example of the table (with just 3 columns): ID Alpha Beta 1 C X 2 P X 3 C T 4 P T 5 C X When I pull this table, I want it to keep rows 2, 3 and 4, excluding 1 and 5 due to the Alpha having C and Beta having X. The pull is very simple, it is simply (code edited with general table name and source): PROC SQL;
CONNECT TO ODBC;
CREATE TABLE TEST1 AS
SELECT *
FROM CONNECTION TO ODBC
(
SELECT *
FROM SOURCE.TABLE
);DISCONNECT FROM ODBC; I am looking for a where statement that will not include rows 1 and 5, as shown in the example above.
... View more
02-13-2020
01:50 PM
Hello, I am pulling data from a database using PROC SQL and there is a WHERE condition I am unsure on how to input. The pull is one table with several columns (variables) and I want to exclude rows when two of the columns have a specific value each. As an example, call the columns Alpha and Beta. If in one row, Alpha has the value C and in that same row, Beta has the value X, I do not want that row in the pull. But if Alpha has the value C and Beta has any other value but X, or if Beta has the value X and Alpha has any other value but C, I do want those rows. Any help on this is appreciated, thanks!
... View more
12-19-2019
06:32 PM
Hello all, I am looking through a SAS program and there are several left joins written that has me scratching my head. To give a simple example, say there are three tables A, B, and C. The coding goes something like this: Select * From A Left Outer Join B On A.ID1 = B.ID1 Left Outer Join C On B.ID2 = C.ID2 This is the first time I have seen left joins set up this way. I don't know if the program is joining tables B and C and then joining that new table to A, or if it joins A and B first, and then joins C to the new combined A/B table. Any clarification on this is appreciated, thanks!
... View more
05-08-2019
04:13 PM
Thank you, this did exactly what I was looking for, much appreciated.
... View more
05-08-2019
03:49 PM
I have a very large table with ID numbers and dates associated with them. As as example of a table with two columns (I will use names instead of ID numbers for simplicity): John 1/1/2010 John 1/1/2011 John 1/1/2012 John 1/1/2013 Sam 6/1/2012 Sam 4/1/2013 Sam 4/1/2014 Victor 7/1/2011 Zack 4/1/2011 Zack 4/1/2012 Zack 10/1/2012 Zack 10/1/2013 From this table, I want to make two tables - One that has the name and the earliest date associated to that name (member ID) and one that has the name and the last date associated to that name. Thus the first table would come out as: John 1/1/2010 Sam 6/1/2012 Victor 7/1/2011 Zack 4/1/2011 And the second table (with the last date) would come out as: John 1/1/2013 Sam 4/1/2014 Victor 7/1/2011 Zack 10/1/2013 I've included the example of "Victor" as there are records with just one date, thus the same date would have to count as both the first and last. Thank you to any that see this and can help!
... View more
12-05-2018
02:47 PM
That did it, thank you so much!
... View more
12-05-2018
02:05 PM
I am pulling data from a Teradata using PROC SQL on a PC-SAS (SAS 9.4 TS Level 1M3) platform. One of the issues I am having is with the date format. In the Teradata, the date is formatted as '2016-01-15' and when I pull it, SAS converts it to a 15JAN2016 format (DATE9. format). I need to convert the date to a YYYYMMDD style (20160115), which I believe would be a character variable (honestly don't know), before exporting the data to a text file. This is the PROC SQL (I've edited the table and column names for simplicity): %LET DATE1_ST_DT = '2018-01-01';
%LET DATE1_END_DT = '2018-01-31';
%LET DATE2_ST_DT = '2016-01-01';
%LET DATE2_END_DT = '2018-02-01';
PROC SQL;
CONNECT TO ODBC(DATAsrc='RANDOMDB');
CREATE TABLE EXAMPLE AS
SELECT * FROM CONNECTION TO ODBC
(
SELECT
A.DATE1,
B.DATE2,
C.DATE3,
C.COSTS
FROM ASYSTEM.TABLE1 A
INNER JOIN ASYSTEM.TABLE2 B
ON A.ID_MATCH1 = B.ID_MATCH1 AND A.ID_MATCH2 = B.ID_MATCH2
INNER JOIN ASYSTEM.TABLE3 C
ON B.ID_MATCH3 = C.ID_MATCH3 AND B.ID_MATCH4 = C.ID_MATCH4
AND B.ID_MATCH5 = C.ID_MATCH5
WHERE A.DATE1 BETWEEN &DATE1_ST_DT AND &DATE1_END_DT
AND B.DATE2 BETWEEN &DATE2_ST_DT AND &DATE2_END_DT
);
QUIT; Ideally I would like to be able to change the format in the same PROC SQL sequence, so that the resulting date variables are in the YYYYMMDD format, thanks!
... View more