Please post the complete code and log so it would be easy to resolve.
If this is how you want to see your results.
PROVISION_ | ||||
Line_Code | DETAIL | Result1 | Result2 | Result3 |
ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ | ||||
1 | 100 | 100 | . | . |
2 | 200 | . | 200 | . |
3 | 300 | . | . | 300 |
The code would be
DATA TEST;
INPUT Line_Code PROVISION_DETAIL;
CARDS;
1 100
2 200
3 300
;
RUN;
PROC SQL;
SELECT TEST.Line_Code,
TEST.Provision_Detail,
max(CASE WHEN Line_Code = 1 THEN Provision_Detail ELSE . END) AS Result1,
max(CASE WHEN Line_Code = 2 THEN Provision_Detail ELSE . END ) AS Result2,
max(CASE WHEN Line_Code = 3 THEN Provision_Detail ELSE . END ) AS Result3
FROM WORK.TEST AS TEST
group by Line_Code;
QUIT;
I'm not sure why you have an AND in the snippet you provided. You definitely don't need a GROUP BY. Your three expressions should be independent.
Here's the code I used to generate the data:
data work.test;
Line_Code = 1; Provision_Detail = 11; output;
Line_Code = 2; Provision_Detail = 12; output;
Line_Code = 3; Provision_Detail = 13; output;
Line_Code = 4; Provision_Detail = 14; output;
Line_Code = 5; Provision_Detail = 15; output;
run;
I used the query builder GUI to build the example I've been presenting, but here's the code that EG generates. I think if you run the code above, and then this, in a code window, you'll get the same results I did
PROC SQL;
CREATE TABLE SASUSER.QUERY_FOR_TEST_0000 AS
SELECT (MAX(CASE WHEN Line_Code = 1 THEN Provision_Detail ELSE . END)) AS Result1,
(MAX(CASE WHEN Line_Code = 2 THEN Provision_Detail ELSE . END)) AS Result2,
(MAX(CASE WHEN Line_Code = 3 THEN Provision_Detail ELSE . END)) AS Result3
FROM WORK.TEST AS TEST;
QUIT;
Keep at it; you're very close!
Tom
The AND is the result of other filters in the code. The Provision Detail is lines of text per group. Each line of text creates another record. The reason I need to take the Provision Detail and put it going across is because if it was left as is (going down) then that increases the size of the file exponentially. Instead of each group having one record, it would have 1-15 records for each group according to how many lines of text in the Provision Detail field. The Line_Order field counts the number of lines of text.
I am close, maybe close in finding I can't do this. I am getting the following error in Enterprise Guide 4.1 NOTE: The query requires remerging summary statistics back with the original data. ERROR: ORACLE execute error: ORA-01652: unable to extend temp segment by 128 in tablespace TMP_01. NOTE: Table WORK.QUERY_FOR_V_MKTDEC_PROVISIONS created, with 0 rows and 13 columns. I am using the following code to create a computed column MAX(CASE When V_MKTDEC_PROVISIONS.LINE_ORDER = 1 Then V_MKTDEC_PROVISIONS.PROVISION_DETAIL ELSE " " END) It wouldn't accept the . so I used " " and it didn't give me an error. I am only testing on 1 record but it doesn't bring back anything. This is a hugh table that I am trying to query.
I got it to work. What was causing the problem was my filtering for the dates. I removed the filter for the dates and got results and the (MAX(CASE When V_MKTDEC_PROVISIONS.LINE_ORDER = 1 Then V_MKTDEC_PROVISIONS.PROVISION_DETAIL ELSE " " END) worked perfectly. I still need to figure out what syntax I can use for the following pseudo code:
Effective_From_Date <= todays date - I need a function for todays date. I was using TODAY() and DATE() but it didn't like those options.
I also have
Effective_to_date is missing or Effective_to_date >= today's date - same problem - I need a function to filter the date fields. Do I need to convert the date fields to some date format?
The reason the . didn't work is because SAS uses different representations for Oracle's NULL; . for numeric variables, blank for character. This is a really old feature of the SAS language.
On the subject of your filter, dates with DBMS tables can be tricky, as different DBMS products represent them differently. DATETIME() is a SAS function that might work. If not, try posting to the "SAS Macro Facility, Data Step and SAS Language Elements" community; someone with more Oracle experience can probably help.
Do you need your filtering statements in the actual MAX(CASE(...)) statements, or can you use a filter on the "Filter Data" tab in your EG query?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.