About tekish

tekish · ‎03-05-2021

Dear expert; how can I use the week function inside my proc report? How can I calculate the weeks and make them the title for each day of the week? week 1: 04Jan21- 08Jan21 week 2: 11Jan21- 05Jan21 week 3: 18Jan21- 22Jan21 data have; length Study $5 Category $9; infile datalines; input study $ datetime : date7. category $ value; return; datalines; x 04Jan21 Category1 10 x 05Jan21 Category2 20 x 06Jan21 Category3 30 x 07Jan21 Category4 40 x 08Jan21 Category1 20 x 11Jan21 Category3 40 x 12Jan21 Category2 27 y 13Jan21 Category1 15 y 15Jan21 Category1 25 y 18Jan21 Category3 35 y 19Jan21 Category4 45 y 20Jan21 Category1 25 y 21Jan21 Category3 45 y 22Jan21 Category2 29 ; run; proc report data=have nowd style(summary)=Header; title ' PROC REPORT'; column study category value,datetime value=tot; define study / group style(column)=Header; define category / group style(column)=Header; define datetime /across order=internal f=date7. 'Date'; define value / analysis sum f=comma6. ' '; define tot / analysis sum 'Total' f=comma6. ; rbreak after / summarize; compute after; study='Total'; endcomp; run;

tekish · ‎03-05-2021

Cynthia_SAS It works. thanks,

tekish · ‎03-03-2021

Here is the sample code I am using. I dont have any idea how to start to code for this one. PROC REPORT DATA= SASHELP.SHOES NOWD HEADLINE HEADSKIP SPLIT='*' STYLE(HEADER)=[ BACKGROUND=ORANGE FONT_WEIGHT = bold]; COLUMN ('Global shoe sales in USD' ('Regional Shoe sales' REGION )('In USD only' SALES)); DEFINE REGION / 'Region' GROUP WIDTH=6 CENTER; DEFINE PRODUCT / 'Product' NOPRINT ORDER GROUP WIDTH=6 CENTER STYLE=[ borderbottomwidth=1]; DEFINE SALES / 'Regional * Sales' SUM RIGHT FORMAT=DOLLAR15.2; BREAK AFTER REGION /style=default ; ENDCOMP; RUN;

tekish · ‎03-03-2021

ChrisNZ, I want to drop the "product" column and merge it to "Region" column.

tekish · ‎03-02-2021

Dear Experts, How can I modify the style in proc report for the following code; PROC REPORT DATA= SASHELP.SHOES NOWD HEADLINE HEADSKIP SPLIT='*' STYLE(HEADER)=[ BACKGROUND=ORANGE FONT_WEIGHT = bold]; COLUMN ('Global shoe sales in USD' ('Regional Shoe sales' REGION PRODUCT)('In USD only' SALES)); DEFINE REGION / 'Region' GROUP WIDTH=6 CENTER; DEFINE PRODUCT / 'Product' ORDER GROUP WIDTH=6 CENTER STYLE=[ borderbottomwidth=1]; DEFINE SALES / 'Regional * Sales' SUM RIGHT FORMAT=DOLLAR15.2; BREAK AFTER REGION /style=default ; ENDCOMP; RUN; expected outcome: Region Regional Sales AFRICA Boot $119,835.00 Men's Casual $562,794.00 Men's Dress $318,500.00 Sandal $190,409.00 Slipper $337,076.00 Sport Shoe $22,150.00 Women's Casual $417,516.00 Women's Dress $374,308.00 ASIA Boot $62,708.00 Men's Casual $11,754.00 Men's Dress $119,366.00 Sandal $8,208.00 Slipper $152,032.00 Sport Shoe $2,092.00 Women's Casual $25,837.00 Women's Dress $78,234.00 CANADA Boot $385,613.00 Men's Casual $441,903.00 Men's Dress $920,101.00 Sandal $14,798.00 Slipper $952,751.00 Sport Shoe $140,389.00 Women's Casual $410,807.00 Women's Dress $989,350.00 CENTRAL AMERICA/CARIBBEAN Boot $190,743.00 Men's Casual $756,513.00 Men's Dress $404,895.00 Sandal $378,382.00 Slipper $883,181.00 Sport Shoe $26,964.00 Women's Casual $399,357.00 Women's Dress $617,718.00 EASTERN EUROPE Boot $306,785.00 Men's Casual $576,396.00 Men's Dress $335,761.00 Sandal $3,716.00 Slipper $509,698.00 Sport Shoe $91,202.00 Women's Casual $209,256.00 Women's Dress $362,126.00 MIDDLE EAST Boot $171,282.00 Men's Casual $2,058,254.00 Men's Dress $839,571.00 Sandal $35,186.00 Slipper $662,480.00 Sport Shoe $4,007.00 Women's Casual $748,792.00 Women's Dress $1,112,207.00 PACIFIC Boot $123,575.00 Men's Casual $662,368.00 Men's Dress $426,191.00 Sandal $48,424.00 Slipper $390,740.00 Sport Shoe $26,169.00 Women's Casual $219,886.00 Women's Dress $399,441.00 SOUTH AMERICA Boot $245,675.00 Men's Casual $544,950.00 Men's Dress $425,669.00 Sandal $165,925.00 Slipper $462,651.00 Sport Shoe $33,061.00 Women's Casual $179,227.00 Women's Dress $377,625.00 UNITED STATES Boot $448,296.00 Men's Casual $1,372,527.00 Men's Dress $969,271.00 Sandal $12,039.00 Slipper $967,927.00 Sport Shoe $104,403.00 Women's Casual $541,536.00 Women's Dress $1,087,987.00 WESTERN EUROPE Boot $296,031.00 Men's Casual $946,248.00 Men's Dress $747,918.00 Sandal $11,349.00 Slipper $857,298.00 Sport Shoe $201,030.00 Women's Casual $985,647.00 Women's Dress $827,479.00

tekish · ‎02-25-2021

Hi Tom, Thanks for your quick reply. you right it is a REPORT. How Do I Change the header for the Date column values? week1:02/01/2021 until 02/05/2021 week2:02/08/2021 until 02/12/2021 week3:02/15/2021 until 02/19/2021 week4:02/22/2021 until 02/26/2021 Week1 02/01/2021 02/02/2021 02/03/2021 02/04/2021 02/05/2021

tekish · ‎02-24-2021

Dear expert. I am trying to transpose the date values into column headers. here is sample data. data have; input state $ regid date:MMDDYY10.; datalines; DC 456789 2/1/2021 DC 450231 2/1/2021 DC 450556 2/2/2021 DC 453246 2/3/2021 DC 458237 2/4/2021 VA 567894 2/8/2021 VA 560233 2/10/2021 VA 560654 2/11/2021 VA 563642 2/11/2021 VA 568732 2/11/2021 CA 678945 2/15/2021 CA 670231 2/15/2021 CA 670556 2/18/2021 CA 673246 2/18/2021 CA 678237 2/18/2021 NM 178945 2/22/2021 NM 170231 2/22/2021 NM 170556 2/26/2021 NM 173246 2/26/2021 NM 178237 2/26/2021 run; sample output template: Wk1 WKL2 WK3 WK4 State 2/1/2021 2/2/2021 2/3/2021 2/4/2021 2/5/2021 2/8/2021 2/9/2021 2/10/2021 2/11/2021 2/12/2021 2/15/2021 2/16/2021 2/17/2021 2/18/2021 2/19/2021 2/22/2021 2/23/2021 2/24/2021 2/25/2021 2/26/2021 DC 456789 450556 453246 458237 DC 450231 VA 567894 560233 560654 VA 563642 CA 678945 670556 CA 670231 678237 NM 178945 170556

tekish · ‎11-25-2020

Thanks Tom it works perfectly.

tekish · ‎11-24-2020

BillM_SAS; Now, i got valid JSON format with more parameter. However, i didn't get the indented output. I have attached "Testfile.txt". I can not attached the jSON file. here the below json format. 1.Is there a way to parse the backslash using SAS for JSON file. 2.How can I get rid of this part so that SAS can read as JSON file.""data": {"json": {"suc": true,"body":,I manually removed the backslash and the first part unilt "[" and JSON engine works for that. </>{ "data": {"json": {"suc": true,"body": "[{\"id\":1,\"kind\":\"de\",\"ser\":\"H2\",\"xid\":\"604-efg\",\"nid\":103,\"ceid\":3,\"rid\":\"a930ca5a994c2ef8\",\"redf\":1,\"nans\":null,\"updateddt\":\"2020-11-10T21\"},{\"id\":2,\"kind\":\"de\",\"ser\":\"H2\",\"xid\":\"604-efg\",\"nid\":115,\"ceid\":2,\"rid\":\"a930-ca5a994c2ef8\",\"redf\":0,\"nans\":null,\"updateddt\":\"2020-11-10T21\"},{\"id\":3,\"kind\":\"de\",\"ser\":\"H2\",\"xid\":\"604-efg\",\"nid\":125,\"ceid\":4,\"rid\":\"a930-ca5a994c2ef8\",\"redf\":1,\"nans\":null,\"updateddt\":\"2020-11-10T21\"},{\"id\":4,\"kind\":\"de\",\"ser\":\"J2\",\"xid\":\"0605-efg\",\"nid\":211,\"ceid\":3,\"nrid\":\"9aea-310c395d271c\",\"redf\":1,\"nans\":null,\"updateddt\":\"2020-11-11T15\"}]" } } }</> how can get I this part only using SAS: [{\"id\":1,\"kind\":\"de\",\"ser\":\"H2\",\"xid\":\"604-efg\",\"nid\":103,\"ceid\":3,\"rid\":\"a930ca5a994c2ef8\",\"redf\":1,\"nans\":null,\"updateddt\":\"2020-11-10T21\"},{\"id\":2,\"kind\":\"de\",\"ser\":\"H2\",\"xid\":\"604-efg\",\"nid\":115,\"ceid\":2,\"rid\":\"a930-ca5a994c2ef8\",\"redf\":0,\"nans\":null,\"updateddt\":\"2020-11-10T21\"},{\"id\":3,\"kind\":\"de\",\"ser\":\"H2\",\"xid\":\"604-efg\",\"nid\":125,\"ceid\":4,\"rid\":\"a930-ca5a994c2ef8\",\"redf\":1,\"nans\":null,\"updateddt\":\"2020-11-10T21\"},{\"id\":4,\"kind\":\"de\",\"ser\":\"J2\",\"xid\":\"0605-efg\",\"nid\":211,\"ceid\":3,\"nrid\":\"9aea-310c395d271c\",\"redf\":1,\"nans\":null,\"updateddt\":\"2020-11-11T15\"}]" } } } libname x json "&Dir.\Testfile.json"; proc print data=x.data_json;run;

tekish · ‎11-21-2020

Hi ballardw; I am getting some data using your code. Is there a way I can improve your code to handle the one line data? The issue is when I save the JSON data into text into saved as one long line(one row). I get also a SAS dataset with all the JSON files in one column. Sample data for text file: data example; informat id $5. kind $5. ser $5. xid $6. nid ceid best8. nrid $10. redf best. nans $25. updateddt yymmdd10.; format updateddt yymmdd10.; infile datalines dlm='[]{},' truncover; input @"id=" id @"kind=" kind @"ser=" ser @"xid=" xid @"nid=" nid @"ceid=" ceid @"nrid=" nrid @; if index(_infile_,"redf=" ) > 0 then input @"redf=" redf @; input @"nans=" nans @"updateddt=" updateddt ; datalines; [{id=1, kind=de, ser=H2, xid=604, nid=103, ceid=3, nrid=894448f2, redf=1, nans=null,updateddt=2018-11-10},{id=2, kind=de, ser=H2, xid=604, nid=115, ceid=2, nrid=724448f2, redf=0, nans=some data available,updateddt=2018-11-10},{id=3, kind=de, ser=H2, xid=604, nid=125, ceid=4, nrid=854448f2, redf=1, nans=some issues,updateddt=2018-11-10},{id=4, kind=de, ser=J2, xid=605, nid=211, ceid=3, nrid=7881dd3d, redf=1, nans=there is data,updateddt=2018-11-11},{id=5, kind=de, ser=J2, xid=605, nid=217, ceid=1, nrid=ffcb48f2, redf=1, nans=test,updateddt=2018-11-11},{id=6, kind=de, ser=J2, xid=605, nid=217, ceid=3, nrid=810c395d, redf=0, nans=attacked, updateddt=2018-11-11},{id=7, kind=de, ser=J3, xid=605, nid=187, ceid=1, nrid=540c395d, redf=0, nans=null,updateddt=2018-11-11},{id=8, kind=de, ser=J2, xid=605, nid=193, ceid=3, nrid=48f2310c, redf=1, nans=the bies,updateddt=2018-11-11},{id=9, kind=de, ser=J2, xid=605, nid=199, ceid=4, nrid=5610c395, red=1, nans=null,updateddt=2018-11-11}] ; run; SAS dataset. is there a way we can use Array? Values [{id=1, kind=de, ser=H2,xid=604,nid=103, ceid=3,nrid=894448f2, redf=1, nans=null,updateddt=2018-11-10}, {id=2, kind=dev, ser=H2,xid=604,nid=115, ceid=2,nrid=724448f2, redf=0, nans=some data available,updateddt=2018-11-10}, {id=3, kind=de, ser=H2,xid=604, nid=125, ceid=4,nrid=854448f2, redf=1, nans=some issues,updateddt=2018-11-10}, {id=4, kind=de, ser=J2,xid=605, nid=211, ceid=3,nrid=7881dd3d, redf=1, nans=there is data,updateddt=2018-11-11}, {id=5, kind=de, ser=J2,xid=605, nid=217, ceid=1,nrid=ffcb48f2, redf=1, nans=test,updateddt=2018-11-11}, {id=6, kind=de, ser=J2,xid=605, nid=217, ceid=3,nrid=810c395d, redf=0,nans=attacked, updateddt=2018-11-11}, {id=7, kind=de, ser=J3,xid=605, nid=187, ceid=1,nrid=540c395d, redf=0, nans=null,updateddt=2018-11-11}, {id=8, kind=de, ser=J2,xid=605, nid=193, ceid=3,nrid=48f2310c, redf=1, nans=the bies,updateddt=2018-11-11}, {id=9, kind=de, ser=J2,xid=605, nid=199, ceid=4,nrid=5610c395, redf=1, nans=null,updateddt=2018-11-11}]

tekish · ‎11-21-2020

Hi Bill, The problem is the data is being fetched from an external website using APIKEY we don't have control over the data structure. We want to create an automated data extraction.

tekish · ‎11-20-2020

Hi Ballardw, I tried the JSON engine and it did not work and then I save the JSON file into a text file. Is there a way I can parse text files using the data step? here I the sample save json.txt file. [{id=1, kind=de, ser=H2,xid=604,nid=103, ceid=3,nrid=894448f2, redf=1, nans=null,updateddt=2018-11-10}, {id=2, kind=de, ser=H2,xid=604,nid=115, ceid=2,nrid=724448f2, redfl=0, nans=some data available,updateddt=2018-11-10}, {id=3, kind=de, ser=H2,xid=604, nid=125, ceid=4,nrid=854448f2, redf=1, nans=some issues,updateddt=2018-11-10}, {id=4, kind=de, ser=J2,xid=605, nid=211, ceid=3,nrid=7881dd3d, redf=1, nans=there is data,updateddt=2018-11-11}, {id=5, kind=de, ser=J2,xid=605, nid=217, ceid=1,nrid=ffcb48f2, redf=1, nans=test,updateddt=2018-11-11}, {id=6, kind=de, ser=J2,xid=605, nid=217, ceid=3,nrid=810c395d, redf=0,nans=attacked, updateddt=2018-11-11}, {id=7, kind=de, ser=J3,xid=605, nid=187, ceid=1,nrid=540c395d, redf=0, nans=null,updateddt=2018-11-11}, {id=8, kind=de, ser=J2,xid=605, nid=193, ceid=3,nrid=48f2310c, redf=1, nans=the bies,updateddt=2018-11-11}, {id=9, kind=de, ser=J2,xid=605, nid=199, ceid=4,nrid=5610c395, red=1, nans=null,updateddt=2018-11-11}]

tekish · ‎11-20-2020

Hi ballardw, This is a JSON file save as josn.txt. I have attached the sample JSON file. here is the my SAS codes: data Have; infile '......\Jsonexample\devjson1.txt' recfm=n dlm='{}[],'; input value : $200. @@; if value in: ('count' 'calls') then delete; run; data want; set Have; length name value $ 100; name=dequote(scan(value,1,'=','m')); values=dequote(scan(value,2,'=','m')); drop value; run;

tekish · ‎11-19-2020

Hi expert, I have data similar with sample data. How do I convert the row values with their respective values in columns. I have attached the sample output. I really appreciate your ideas or suggestions. sample data: NAMES VALUES id 1 kind de ser H2 xid 604 nid 103 ceid 3 nrid 624448f2-8263 redf 1 nans null updateddt 11/10/2018 id 2 kind de ser H2 xid 604 nid 115 ceid 2 nrid 6244-8263-44 redf 0 nans null updateddt 11/10/2018 id 3 kind de ser J2 xid 604 nid 125 ceid 4 nrid 62f3ff48-8263 redf 1 nans null updateddt 11/10/2018 id 4 kind de ser J4 xid 605 nid 211 ceid 3 nrid 9aea-31cddfd7 redf 1 nans null updateddt 11/10/2018 id 5 kind de ser J4 xid 605 nid 217 ceid 1 nrid 9aea-310c redf 1 nans null updateddt 11/10/2018 id 6 kind de ser J4 xid 605 nid 217 ceid 3 nrid 562f3ff48-8263 redf 0 nans attacked updateddt 11/10/2018 Sample Out put. ID Kind ser xid nid ceid nrid redf nans updatedt 1 de H2 604 103 3 424448f2-8263 1 null 11/10/2018 2 de H2 604 115 2 4244-8263-44 0 null 11/10/2018 3 de J2 604 125 4 562f3ff48-8263 1 null 4 de J4 605 211 3 9aea-31cddfd7 1 null 11/10/2018 5 de J4 605 217 1 9aea-310c 1 null 11/10/2018 6 de J4 605 217 3 562f3ff48-8263 0 attacked 11/10/2018

tekish · ‎11-11-2020

Hi Experts, I have a program which connects SAS to AWS. How do I read the json file. I any suggestion would be appreciated. here is my json file sample code: data temp; length attribute body $800; infile datalines; input @; if _infile_ ne: '{' and _infile_ ne: '}'; input attribute: $quote. x $ body: $quote.; datalines; {"data":{"Read":{"success":true,"body":"[ {qr=34, db=test, usertest=test1234, exid=0880-1, queid=1, chid=2, openans=null, updateddt=2020-09-03T16:30:43.125Z, qrnid=t09990-12020-09-03 16:3}, {qr=265, db=test, usertest=usertoken, xid=60, qid=1, chid=1, openans=null, updateddt=2020-09-08T17:24:48.927Z, qrnid=t06042020}, {qr=266, db=test, usertest=usertoken, xid=64, qid=2, chid=1, openans=null, updateddt=2020-09-08T17:24:48.927Z, qrnid=t06042020 }, {qr=267, db=test, usertest=usertoken, xid=64, qid=3, chid=2, openans=null, updateddt=2020-09-08T17:24:48.927Z, qrnid=t06042020 }, {qr=268, db=test, usertest=usertoken, xid=65, qid=5, chid=3, openans=null, updateddt=2020-09-08T17:24:48.927Z, qrnid=t06042020 }, {qr=269, db=test, usertest=usertoken, xid=66, qid=5, chid=4, openans=null, updateddt=2020-09-08T17:24:48.927Z, qrnid=t06042020 }, {qr=270, db=test, usertest=usertoken, xid=66, qid=6, chid=1, openans=null, updateddt=2020-09-08T17:24:48.927Z, qrnid=t06042020 }, {qr=271, db=test, usertest=usertoken, xid=65, qid=7, chid=1, openans=error, updateddt=2020-09-08T17:24:48.927Z, qrnid=t06042020}, {qr=272, db=test, usertest=usertoken, xid=65, qid=1, chid=2, openans=null, updateddt=2020-09-08T17:25:57.096Z, qrnid=t06042020 }, ; run;

Online Status	Offline
Date Last Visited	‎08-31-2021 04:44 PM

how to calculate week function inside proc report

Re: How to style report in proc report

Re: How to style report in proc report

Re: How to style report in proc report

How to style report in proc report

Re: how to transpose row into columns

how to transpose row into columns

Re: how to convert row data into columns

Re: how to convert row data into columns

Re: how to convert row data into columns

Re: How to style report in proc report

Re: How to add missing columns in existing dataset

Re: How to transpose column

Re: Reorder subrow rownumber

Re: Reorder the rownumber

Re: API connection Using SAS

Re: Reorder subrow rownumber

how to calculate week function inside proc report

Re: How to style report in proc report

Re: How to style report in proc report

Re: How to style report in proc report

How to style report in proc report

Re: how to transpose row into columns

how to transpose row into columns

Re: how to convert row data into columns

Re: how to convert row data into columns

Re: how to convert row data into columns

Re: how to convert row data into columns

Re: how to convert row data into columns

Re: how to convert row data into columns

how to convert row data into columns

Convert Json file from AWS