cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
scb
‎05-19-2021
  • 118 Posts
  • 3 Likes Given
  • 0 Solutions
  • 2 Likes Received

proc sql query

by Obsidian | Level 7 in SAS Programming
‎03-20-2017 05:13 AM
‎03-20-2017 05:13 AM
When I tried to run the below code, I would like  to count the number of distinct product (except product “ABE”) for every ID, but it turned out all product are equal to 1.    My desired output would be:   ID         Product          Amount 123         3                    90000 124         2                   170000 125         2                    36180   Anyone can help? Thanks.   data test; input product $ id $ amt; datalines; ABC 123 10000 ABD 123 20000 ABD 123 40000 ABE 123 10000 ABC 124 10000 ABD 124 20000 ABC 124 60000 ABE 124 80000 ABC 125 13000 ABD 125 23180 ; run; proc sql; create table TEST1 as select ID, sum(DISTINCT case when PRODUCT="ABE" then 0 else 1 end) as PRODUCT, sum(AMT) as AMT from TEST group by ID; quit;   ... View more

SAS DATE CONVERSION QUERY

by Obsidian | Level 7 in SAS Programming
‎03-15-2017 06:08 AM
‎03-15-2017 06:08 AM
May I know how to convert the data to date9. format? Thanks. ... View more

SAS Base Query

by Obsidian | Level 7 in SAS Programming
‎03-15-2017 03:27 AM
‎03-15-2017 03:27 AM
May I know how to get the output from the following input? Thanks.   ID      ABC BBC 123   1      1 124   1      2 125   0      3     DATA A; INFILE CARDS; INPUT ID $ ACCT $; CARDS; 123 ABC 123 BBC 124 ABC 124 BBC 124 BBC 125 BBC 125 BBC 125 BBC ; RUN; ... View more

SAS Base Query

by Obsidian | Level 7 in SAS Programming
‎03-02-2017 08:47 PM
‎03-02-2017 08:47 PM
The below codes is working fine.  Just want to know if there is any way to have to same result without RENAME and DROP variables? Thanks.   DATA A; INFILE CARDS; INPUT ID ; CARDS; 3453 123162 124171 1241761 ; RUN; DATA A(RENAME=(ID1=ID)); SET A; ID1 = PUT(ID,z7.); DROP ID; RUN; ... View more

ODS PDF TEXT QUERY

by Obsidian | Level 7 in ODS and Base Reporting
‎03-01-2017 04:03 AM
‎03-01-2017 04:03 AM
I would like to put ABC appear same as LINE13 vertically, but I just could not get it. Anyone can help? Thanks.     ods pdf file='d:\odstest.pdf'; ods escapechar='^';   proc report data=sashelp.class(obs=1) nowd;   compute after /style={just=l leftmargin=3in};  endcomp; run;    ods pdf text='^S={leftmargin=.75in}cde ^S={leftmargin=5.25in}ABC'; ods pdf text='^S={leftmargin=5.25in}LINE13';   ods pdf close; ... View more

SAS Base Query

by Obsidian | Level 7 in SAS Programming
‎02-27-2017 04:53 AM
‎02-27-2017 04:53 AM
CustNo 12345671819 17289201010   May I know how to disguise the 5th to 8th digit of the CustNo from number to XXXX? Thanks. ... View more

ODS Query

by Obsidian | Level 7 in ODS and Base Reporting
‎02-23-2017 05:16 AM
‎02-23-2017 05:16 AM
I have tested the below code that can generate the pdf report; and would like to know if there is any way of putting the below example (for sign off purpose) at the end of the report.  Thank you very much.   For example:   Prepared By:                              Approved By:     (Colin Bell)                                (Toby Campbell) Executive                                  Director     My test code: ods pdf file="d:\test.pdf" contents = yes; ods proclabel "Ms. Crabapple's Class"; title 'Class Health Information'; data _null_; file print ods; set sashelp.class; put _ods_; run; ods pdf close; ... View more

PROC SQL - UPDATE

by Obsidian | Level 7 in SAS Programming
‎02-16-2017 09:51 PM
‎02-16-2017 09:51 PM
Any idea to correct the syntax below? PROC SQL; UPDATE TABLEA SET NAME='',ACCOUNT='' WHERE ID =(SELECT ID FROM TABLEB WHERE AMOUNT LE 0); QUIT;   I GOT THIS ERROR MESSAGE: ERROR: Subquery evaluated to more than one row. Many Thanks. ... View more

Re: SAS query

by Obsidian | Level 7 in SAS Programming
‎02-13-2017 05:15 AM
‎02-13-2017 05:15 AM
It is not blank. There is value in there. Please refer to the program. DATA TEST; INPUT CASE $ PRODUCT $ AMT DATE DATE9.; FORMAT DATE DATE9.; DATALINES; PL123 PL 6000 01JAN2017 PL123 PL 6000 01JAN2017 PL172 PL 6000 06JAN2017 PL173 PL 6000 07JAN2017 PL173 PL 6000 08JAN2017 PL173 PL 6000 09JAN2017 PL173 PL 6000 01JAN2017 PL173 PL 6000 02JAN2017 ; RUN; data TEST; RETAIN DATE CASE PRODUCT AMT MONTH1-MONTH30; FORMAT MONTH1-MONTH30 DOLLAR8.2; set test; array month{30}; do i=1 to 30; month{i}=(((30-i)+1)/30)*amt; end; run; ... View more

Re: SAS query

by Obsidian | Level 7 in SAS Programming
‎02-13-2017 05:07 AM
‎02-13-2017 05:07 AM
Sorry Kurt, I don't really understand the question. Any example? Thanks. ... View more

Re: SAS query

by Obsidian | Level 7 in SAS Programming
‎02-13-2017 05:00 AM
‎02-13-2017 05:00 AM
It will not be empty...all will be numeric. ... View more

Re: SAS query

by Obsidian | Level 7 in SAS Programming
‎02-13-2017 04:55 AM
‎02-13-2017 04:55 AM
I would like change the change the name of the month1-month30 variable names; for example, If the date is in the range of Jan17 (in the date column, all date is in the range of Jan17), then month1 will be JAN17, month2 will be FEB17, month3 will be MAR17. If the date is in the range of OCT17, then month1 will be OCT17, month2 will be NOV17, month3 will be DEC17, month4 will be JAN18. ... View more

SAS query

by Obsidian | Level 7 in SAS Programming
‎02-13-2017 04:19 AM
‎02-13-2017 04:19 AM
I have got the 2 datasets; test and test1, both have month1 to month 30.   May I know how to get the change the name of month1 to month30? If the date is in the range of Jan17, then month1 will be JAN17, month2 will be FEB17, month3 will be MAR17.  If the date is in the range of OCT17, then month1 will be OCT17, month2 will be NOV17, month3 will be DEC17, month4 will be JAN18.   Any idea, thanks.   DATA TEST; INPUT CASE $ PRODUCT $ AMT DATE DATE9.; FORMAT DATE DATE9.; DATALINES; PL123 PL 6000 01JAN2017 PL123 PL 6000 01JAN2017 PL172 PL 6000 06JAN2017 PL173 PL 6000 07JAN2017 PL173 PL 6000 08JAN2017 PL173 PL 6000 09JAN2017 PL173 PL 6000 01JAN2017 PL173 PL 6000 02JAN2017 ; RUN; DATA TEST1; INPUT CASE $ PRODUCT $ AMT DATE DATE9.; FORMAT DATE DATE9.; DATALINES; PL123 PL 6000 01OCT2017 PL123 PL 6000 01OCT2017 PL172 PL 6000 06OCT2017 PL173 PL 6000 07OCT2017 PL173 PL 6000 08OCT2017 PL173 PL 6000 09OCT2017 PL173 PL 6000 01OCT2017 PL173 PL 6000 02OCT2017 ; RUN; data TEST; RETAIN DATE CASE PRODUCT AMT MONTH1-MONTH30; FORMAT MONTH1-MONTH30 DOLLAR8.2; set test; array month{30}; run; data TEST1; RETAIN DATE CASE PRODUCT AMT MONTH1-MONTH30; FORMAT MONTH1-MONTH30 DOLLAR8.2; set test1; array month{30}; run; ... View more

Re: PROC SQL QUERY

by Obsidian | Level 7 in SAS Programming
‎02-02-2017 06:11 AM
‎02-02-2017 06:11 AM
PROC SQL; 740 CREATE TABLE TEST1 AS SELECT DISTINCT 741 ID, 742 CASE WHEN PRODUCT NOT IN ('ABE') THEN COUNT(DISTINCT PRODUCT)-1 ELSE COUNT(DISTINCT 742! PRODUCT) END AS PRODUCTS, 743 SUM(AMT) AS AMT 744 FROM TEST 745 GROUP BY ID 746 HAVING PRODUCTS IN (SELECT MIN(PRODUCT) FROM TEST WHERE PRODUCT NOT IN 'ABE'); ----- 22 76 ERROR 22-322: Syntax error, expecting one of the following: (, SELECT. ERROR 76-322: Syntax error, statement will be ignored. 747 QUIT; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.01 seconds ... View more

Re: PROC SQL QUERY

by Obsidian | Level 7 in SAS Programming
‎02-02-2017 06:10 AM
‎02-02-2017 06:10 AM
The answer that I need:   ID   Product    Amt 123  2            70000 124  2            98700 125  1            343100 ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎05-19-2021 06:07 AM
Likes given to
Likes from
View All
Quick links