About sufiya

sufiya · ‎07-06-2022

Hi Kurt_Bremser this works but I end up with a blank/null last row - is there anyway to remove the row if all fields are null? thank you, Sof

sufiya · ‎07-06-2022

Hi @Kurt_Bremser - sorry I thought I did attached the csv file.

sufiya · ‎07-05-2022

Hi, Hoping someone can help and correct the logic I am using. THank you in advance 😉 Trying to import a csv file that is separated by semicolon and the field names start on row 2 and data starts row 3 csv file shoes_startrow.csv (attached): syntax: proc import datafile="C:\Users\Other Request\shoes_startrow.csv" out=Shoe_File dbms=csv replace; GETNAMES=NO; DELIMITER=';'; DATAROW=2; run; result: want: Region Product Subsidiary Stores Sales Inventory Returns Africa Boot Addis Ababa 12 $29,761 $191,821 $769 Africa Men's Casual Addis Ababa 4 $67,242 $118,036 $2,284 Africa Men's Dress Addis Ababa 7 $76,793 $136,273 $2,433 Africa Sandal Addis Ababa 10 $62,819 $204,284 $1,861 Africa Slipper Addis Ababa 14 $68,641 $279,795 $1,771 Africa Sport Shoe Addis Ababa 4 $1,690 $16,634 $79 Africa Women's Casual Addis Ababa 2 $51,541 $98,641 $940 Africa Women's Dress Addis Ababa 12 $108,942 $311,017 $3,233

sufiya · ‎02-07-2022

Thank you this works!

sufiya · ‎02-07-2022

Hi, I have a column from which I need to extract text after a specific index word (MP).using the following code: catx(" ", scan(TEST_NAME , 2, "MP")) as NAME, this is what I end up with: TEST_NAME NAME MP KONSTANTINE ABCDE WNP BM OPS TEST MP CIGI FANNING BM O MP DAN BkENTHER ERA MONTREAL TEST MP MARIA PUBRANTI MARIA PUBRANTI MP MARIA PUBRANTI MP BRUCE WAYNE MYABCD DIG CHAN TEST MP ABCDE DADOUN ABCDE DADOUN MP JENNIFER NOSNIKOF OSNIKOF MP STEVE PETERS E PETERS below is what I want - However, I can't seem to get the code right to extract the text after "MP", any suggestions, please? Kind regards 🙂 NAME KONSTANTINE ABCDE CIGI FANNING DAN BkENTHER MARIA PUBRANTI MARIA PUBRANTI BRUCE WAYNE ABCDE DADOUN MP JENNIFER NOSNIKOF STEVE PETERS

sufiya · ‎07-17-2020

@Kurt_Bremser , @PaigeMiller , @ballardw , @SASKiwi Thank you for the help guys! Figured it out, just needed to remove the 'Group By' + tweaked the CASEstatement and it ran with no errors. PROC SQL; CREATE TABLE BUCKETS AS SELECT DISTINCT a.MONTH, a.YEAR, a.ANIMAL, a.BAN, a.VOLUME, a.SIGHTINGS, (case when a.SIGHTINGS >= 0 and a.SIGHTINGS <= 49.99 then '1.0-49' when a.SIGHTINGS >= 50 and a.SIGHTINGS <= 99.99 then '2.50-99' when a.SIGHTINGS >= 100 and a.SIGHTINGS <= 249.99 then '3.100-249' when a.SIGHTINGS >= 250 and a.SIGHTINGS <= 499.99 then '4.250-499' when a.SIGHTINGS >= 500 and a.SIGHTINGS <=999.99 then '5.500-999' when a. SIGHTINGS >= 1000 then '6.1000+' else '' end) as Buckets FROM WORK.MERGE a ;QUIT;

sufiya · ‎07-17-2020

@PaigeMiller , please see below for the full error message. Thank you, 36 (case _ 22 ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, FROM, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=. 37 when a. SIGHTINGS >= 0 and a. SIGHTINGS <= 49.99 then '1.0-49' ____ 22 76 ERROR 22-322: Syntax error, expecting one of the following: a name, (, ',', '.', ANSIMISS, AS, CROSS, FULL, INNER, JOIN, LEFT, NATURAL, NOMISS, RIGHT. ERROR 76-322: Syntax error, statement will be ignored. 38 when >= 50 a.SIGHTINGS <= 99.99 then '2.50-99' 39 when >= 100 a. SIGHTINGS <= 249.99 then '3.100-249' 40 when >= 250 a.SIGHTINGS <= 499.99 then '4.250-499' 41 when >= 500 a. SIGHTINGS <=999.99 then '5.500-999' 42 when a. SIGHTINGS >= 1000 then '6.1000+' 43 else '' end) as Buckets 44

sufiya · ‎07-17-2020

Hello @SASKiwi , I did that and still getting the same error message.

sufiya · ‎07-16-2020

Hello, Hoping someone can help... trying to execute a Case When statement but I keep receiving this error message: ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, FROM, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=. Below is the PROC SQL code and attached is the data - I have no idea what I am missing 😞 Thank you !! PROC SQL; CREATE TABLE BUCKETS AS SELECT DISTINCT a.MONTH, a.YEAR, a.ANIMAL, a.BAN, a.VOLUME, a.SIGHTINGS, (case when a. SIGHTINGS >= 0 and a. SIGHTINGS <= 49.99 then '1.0-49' when >= 50 a.SIGHTINGS <= 99.99 then '2.50-99' when >= 100 a. SIGHTINGS <= 249.99 then '3.100-249' when >= 250 a.SIGHTINGS <= 499.99 then '4.250-499' when >= 500 a. SIGHTINGS <=999.99 then '5.500-999' when a. SIGHTINGS >= 1000 then '6.1000+' else '' end) as Buckets FROM WORK.MERGE a GROUP BY a.Month, a.YEAR, a.ANIMAL, a.BAN, a.VOLUME, a.SIGHTINGS ;QUIT;

sufiya · ‎05-29-2020

thank you!

sufiya · ‎05-29-2020

Hi @ghosh Thank you - it worked! I have a question, I want to have the first column 'Color' variables repeat but they don't - how can I update the query for this? was adding to get column variables to repeat: COMPUTE color; IF color NE ' ' then hold=color ; IF color EQ ' ' then color=hold ; ENDCOMP; full code: proc report data=WORK.RawData nowd spanrows; column color Code Model Volume, Day (Volume=tot); define color / group order=data; define Code / group ; define Model / group ; define Day / across ' '; define Volume / analysis sum '' ; define tot / analysis sum 'Total' f=6.0 ; COMPUTE color; IF color NE ' ' then hold=color ; IF color EQ ' ' then color=hold ; ENDCOMP; rbreak before / summarize; compute before; color='Sum'; endcomp; run; ODS HTML CLOSE; quit;

sufiya · ‎05-28-2020

Hi @ghosh - uploaded csv version - thank you again!

sufiya · ‎05-28-2020

Hi @ed_sas_member & @ghosh - attached the raw data- thank you both!

sufiya · ‎05-27-2020

Hello, Hoping if someone can please help me and let me know how to make 2 rows align into 1 row and create a total column? Thank you in advance !!! this is what I have : 19-May-20 20-May-20 21-May-20 22-May-20 Color Code Model Total Total Total 16 10 11 21 Red AAB Ceiling Fan 1 2 . 5 Red CCA Air Conditioner 15 7 11 15 Blue CCA Air Conditioner . 1 . 1 proc report data=Summary_Vol nowd; column color Code Model Day , Volume; define color / group 'color' ; define Code / group 'Code'; define Model / group 'Volume'; define Day / across ''; define Volume / analysis sum ''; COMPUTE segment; IF color NE ' ' then hold=color ; IF color EQ ' ' then color=hold ; ENDCOMP; rbreak before / summarize; compute before; if color = "" then color = 'Total'; if Code = "" then Code = 'Total'; if Model = "" then Model = 'Total'; ENDCOMP; run; This is what I want to get: Color Code Model 19-May-20 20-May-20 21-May-20 22-May-20 Total Total Total Total 16 10 11 21 58 Red AAB Ceiling Fan 1 2 . 5 8 Red CCA Air Conditioner 15 7 11 15 48 Blue CCA Air Conditioner . 1 . 1 2

sufiya · ‎05-20-2020

Thank you @Kurt_Bremser 😊

Online Status	Offline
Date Last Visited	‎07-09-2022 03:48 PM

Re: PROC IMPORT of CSV file with semicolon delimiter and while removin...

Re: PROC IMPORT of CSV file with semicolon delimiter and while removin...

PROC IMPORT of CSV file with semicolon delimiter and while removing le...

Re: Using CATX to extract text after a specific index word

Using CATX to extract text after a specific index word

Re: PROC SQL: CASE WHEN statement ERROR

Re: PROC SQL: CASE WHEN statement ERROR

Re: PROC SQL: CASE WHEN statement ERROR

PROC SQL: CASE WHEN statement ERROR

Re: Proc Report make 2 rows align into 1 row and create a total column

Re: Using CATX to extract text after a specific index word

Re: Proc transpose add grand total row

Re: Proc transpose add grand total row

Re: Proc SQL equivalent to get previous month from current month date

Re: Proc SQL equivalent to get previous month from current month date

How to calculate number of months between 2 dates

Re: How to calculate number of months between 2 dates

Re: How to calculate number of months between 2 dates

Re: Proc Report make 2 rows align into 1 row and create a total column

Re: Proc transpose add grand total row

Re: PROC IMPORT of CSV file with semicolon delimiter and while removin...

Re: PROC IMPORT of CSV file with semicolon delimiter and while removin...

PROC IMPORT of CSV file with semicolon delimiter and while removing le...

Re: Using CATX to extract text after a specific index word

Using CATX to extract text after a specific index word

Re: PROC SQL: CASE WHEN statement ERROR

Re: PROC SQL: CASE WHEN statement ERROR

Re: PROC SQL: CASE WHEN statement ERROR

PROC SQL: CASE WHEN statement ERROR

Re: Proc Report make 2 rows align into 1 row and create a total column

Re: Proc Report make 2 rows align into 1 row and create a total column

Re: Proc Report make 2 rows align into 1 row and create a total column

Re: Proc Report make 2 rows align into 1 row and create a total column

Proc Report make 2 rows align into 1 row and create a total column

Re: SAS Code for Converting Number Value to Date Format