About Junyong

Junyong · ‎09-20-2021

Sorry for this repeated bothering, but what if I need to erase the last two or three characters? I found that my previous code attaches either CRLF or LF right after the last semicolon. data _null_; infile "https://finance.yahoo.com/quote/KO/financials?p=KO" url lrecl=3276700 truncover start=S; input HEAD $80.; file "!USERPROFILE\Desktop\WANT.json" lrecl=3276700; if HEAD=:"root.App.main" then do; S=index(HEAD,"{"); put _infile_; end; run; The last three characters (LF, CR, and then the semicolon) can be deleted by repeating your code three times, but I wonder if there is a solution smarter than this.

Junyong · ‎09-19-2021

Thanks for these details. Here's my modified code, data _null_; infile "https://finance.yahoo.com/quote/KO/financials?p=KO" url lrecl=3276700 truncover start=S; input HEAD $80.; file "!USERPROFILE\Desktop\WANT.json" lrecl=3276700; if HEAD=:"root.App.main" then do; S=index(HEAD,"{"); put _infile_; end; run; and the outcome ending with :{"strings":1},"yahoodotcom-layout":{"strings":1}}},"options":{"defaultBundle":"td-app-finance"}}}}; I wanted to also remove the very last semicolon in the outcome JSON. The problem then is that an END and LAST approach, unlike the START and HEAD approach above, cannot be used when deleting the right-end semicolon (for example, END=INDEX(_INFILE_,";")-1 is impossible). How should I do in this case?

Junyong · ‎09-18-2021

Yahoo Finance HTMLs contain JSON data in the middle as follows (I somewhat truncated the HTML to show how it looks): <!DOCTYPE html><html id="atomic" class="NoJs desktop" lang="en-US"><head prefix="og: http://ogp.me/ns#"><script>window.performance && window </script><script>!function(e,s,f,p){var a=[],t={_version:"3.11.4",_config:{classPrefix:"",enableClasses:!0,enableJSClass:!0,usePrefixes:!0}, if (!window.YAHOO || !window.YAHOO.i13n || !window.YAHOO.i13n.Rapid) { return; } var rapidConfig = {"async_all_clicks":true,"click_timeout":300,"client_only":1,"compr_type":"deflate","keys":{"ver":"ydotcom","navtype":"ser window.rapidInstance = new window.YAHOO.i13n.Rapid(rapidConfig); })();</script></head><body><div id="app"><div class="" data-reactroot="" data-reactid="1" data-react-checksum="1106760844"><div data-reactid window._adPerfData = []; window._adPosMsg = []; window._perfMark = function _perfMark (name) {if (window.performance && window.performance.mark){try {if (window.performance.getEntriesByNam window._perfMeasure = function _perfMeasure (name, start, end) {if (window.performance && window.performance.measure){try {if (window.perfor window._pushAdPerfMetric = function _pushAdPerfMetric(key) {if (window.performance && window.performance.now) {_adPerfData.push([key, Math.r window._fireAdPerfBeacon = function _fireAdPerfBeacon(eventName) {try {if (window && window.rapidInstance && window.performance) {var navCli window.rapidInstance.beaconPerformanceData(perfData);}} catch (e) {console.warn('Could not send the beacon:',e);}}; window.DARLA_CONFIG = {"debug":false,"dm":1,"autoRotation":10000,"rotationTimingDisabled":true,"k2":{"res":{"rate":5,"pos":["BTN","BTN-1","B window.DARLA_CONFIG.servicePath = window.location.protocol + "//fc.yahoo.com/sdarla/php/fc.php";window.DARLA_CONFIG.dm = 1;window.DARLA_CONF window.DARLA_CONFIG.onFinishRequest = function() {window._perfMark('DARLA_REQEND');}; window.DARLA_CONFIG.onStartParse = function() {window._perfMark('DARLA_PSTART');}; window.DARLA_CONFIG.onSuccess = function(eventName) {if (eventName === 'AUTO') {return;}if (window._DarlaEvents) {window._DarlaEvents.emit(" window.DARLA_CONFIG.onStartPosRender = function(posItem) {var posId = posItem && posItem.pos;window._perfMark('DARLA_ADSTART_' + posId);if ( window.DARLA_CONFIG.onFinishPosRender = function(posId, reqList, posItem) {var ltime;window._perfMark('DARLA_ADEND_' + posId);window._perfMe window.DARLA_CONFIG.onBeforePosMsg = function(msg, posId) {var maxWidth = 970, maxHeight = 600;var newWidth, newHeight, pos;if (window._Darl window.DARLA_CONFIG.onFinishParse = function(eventName, response) {try {if (eventName !== "AUTO") {var positionlist = response.ps();var foun }; window.DARLA_CONFIG.onStartPrefetchRequest = function(eventName) {window._perfMark('DARLA_PFSTART');}; window.DARLA_CONFIG.onFinishPrefetchRequest = function(eventName, status) {window._perfMark('DARLA_PFEND');try {window._DarlaEvents.emit('fi }; window.DARLA_CONFIG.onPosMsg = function(cmd, pos, msg) {try {if (window._DarlaEvents && cmd === "cmsg") {var posmsg = {pos: pos,msg: msg};wi }; (function () {var _onloadEvt = function _onloadEvtHandler() {window._loadEvt = true;if (window._darlaSuccessEvt) {window._fireAdPerfBeacon(w window.$sf = window.sf = {}; window.$sf.host = {onReady: function (autorender, deferrender, firstRenderPos, deferRenderDelay) {window._perfMark('DARLA_ONREADY');window._ }; window.sf_host = window.$sf.host; document.onreadystatechange = function () {if (document.readyState == "interactive") {window._perfMark('DOM_INTERACTIVE');}};</script> <script type="text/x-safeframe" id="fc" _ver="4-9-0">{"positions":[{"id":"FB2A","html":"\n","lowHTML":"","meta":{"y"  <script type="text/javascript">if (typeof DARLA !== "undefined" && DARLA) {DARLA.config(window.DARLA_CONFIG);window.sf_host.onReady(true,tru <style> .hide-lrec-ad .darla-lrec-ad {display: none;}.hide-non-lrec34 .darla-nonlrec34-ad {display: none;}.hide-non-lrec34 .sticky-outer-wrapper .td </div><script> (function (root) { /* -- Data -- */ root.App || (root.App = {}); root.App.now = 1631982394842; root.App.main = {"context":{"dispatcher":{"stores":{"PageStore":{"currentPageName":"quote","currentEvent":{"eventName":"NEW_PAGE_SUCCESS"}," }(this)); </script><script> (function(win) { win.vzm = win.vzm || {}; The sixth line from the bottom above contains a JSON value root.App.main. I wanted to isolate these JSON data, create a new JSON file WANT.json as follows (a trouble occurred at this point), and then read the created JSON using libname JSON. filename HAVE temp; proc http url="https://finance.yahoo.com/quote/KO/financials?p=KO" out=HAVE; run; data _null_; infile HAVE; input; file "/home/junyong0/WANT.json"; if _infile_=:"root.App.main" then put _infile_; run; The problem is the 32,767 LRECL limit applied to _INFILE_. The resultant WANT.json contains only the first 32,767 letters truncating all the remainders. Is there any way to handle this long line to get an appropriate JSON file? Or I wonder if there is a simpler way to read HTML-embedded JSON.

Junyong · ‎09-07-2021

My initial understanding was that passing the five pieces— "&sheet$A19:%sysfunc(ifc(" , &sheet , "=" , RF , and ",B,AD))0" —for the range is impossible as it demands one single- or double-quoted string, but it seems it works with the following note 49-169 . 1 filename tmp temp; 2 3 proc http 3 ! url="https://images.aqr.com/-/media/AQR/Documents/Insights/Data-Sets/Qualit 3 ! y-Minus-Junk-Factors-Monthly.xlsx" out=tmp; 4 run; NOTE: PROCEDURE HTTP used (Total process time): real time 0.95 seconds cpu time 0.03 seconds NOTE: 200 OK 5 6 %macro import(sheet); 7 8 proc import file=TMP dbms=xlsx replace out=IMPORTED ; 9 range="&sheet$A19:%sysfunc(ifc("&sheet"="RF",B,AD))0"; --- 49 NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space between a quoted string and the succeeding identifier is recommended. 10 run; 11 12 data TABLEJ; 13 length SRC $11; 14 set %sysfunc(ifc("&sheet"="MKT", ,TABLEJ )) IMPORTED ; 15 where DATE > . ; 16 if src=' ' then src="&sheet"; 17 run; 18 19 %mend; 20 21 proc delete data=IMPORTED; run; WARNING: File WORK.IMPORTED.DATA does not exist. NOTE: PROCEDURE DELETE used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 22 23 %import(MKT) NOTE: Variable Name Change. Global Ex USA -> Global_Ex_USA NOTE: Variable Name Change. North America -> North_America NOTE: The import data set has 1869 observations and 30 variables. NOTE: WORK.IMPORTED data set was successfully created. NOTE: PROCEDURE IMPORT used (Total process time): real time 0.48 seconds cpu time 0.47 seconds NOTE: There were 1141 observations read from the data set WORK.IMPORTED. WHERE DATE>.; NOTE: The data set WORK.TABLEJ has 1141 observations and 31 variables. NOTE: DATA statement used (Total process time): real time 0.03 seconds cpu time 0.01 seconds 24 %import(QMJ Factors) NOTE: Variable Name Change. Global Ex USA -> Global_Ex_USA NOTE: Variable Name Change. North America -> North_America NOTE: The import data set has 1869 observations and 30 variables. NOTE: WORK.IMPORTED data set was successfully created. NOTE: PROCEDURE IMPORT used (Total process time): real time 0.48 seconds cpu time 0.48 seconds NOTE: There were 1141 observations read from the data set WORK.TABLEJ. WHERE DATE>.; NOTE: There were 769 observations read from the data set WORK.IMPORTED. WHERE DATE>.; NOTE: The data set WORK.TABLEJ has 1910 observations and 31 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 25 %import(ME(t-1)) NOTE: Variable Name Change. Global Ex USA -> Global_Ex_USA NOTE: Variable Name Change. North America -> North_America NOTE: The import data set has 1969 observations and 30 variables. NOTE: WORK.IMPORTED data set was successfully created. NOTE: PROCEDURE IMPORT used (Total process time): real time 0.31 seconds cpu time 0.31 seconds NOTE: There were 1910 observations read from the data set WORK.TABLEJ. WHERE DATE>.; NOTE: There were 1141 observations read from the data set WORK.IMPORTED. WHERE DATE>.; NOTE: The data set WORK.TABLEJ has 3051 observations and 31 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.00 seconds 26 %import(RF) NOTE: Variable Name Change. Risk Free Rate -> Risk_Free_Rate NOTE: The import data set has 1830 observations and 2 variables. NOTE: WORK.IMPORTED data set was successfully created. NOTE: PROCEDURE IMPORT used (Total process time): real time 0.39 seconds cpu time 0.39 seconds NOTE: There were 3051 observations read from the data set WORK.TABLEJ. WHERE DATE>.; NOTE: There were 1141 observations read from the data set WORK.IMPORTED. WHERE DATE>.; NOTE: The data set WORK.TABLEJ has 4192 observations and 32 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds

Junyong · ‎09-07-2021

I used the double-double quotes because I wanted double-quoted strings "&i" and "RF" inside the already double-quoted string passed for range . proc import file=i dbms=xlsx replace out=i; range="&i$A19:%sysfunc(ifc(""&i""=""RF"",B,AD))0"; run; I use ifc rather than %if / %then / %else to shorten—of course, the following code would be more explicit and cause no error. proc import file=i dbms=xlsx replace out=i; %if &i=RF %then %do; range="&i$A19:B0"; %end; %else %do; range="&i$A19:AD0"; %end; run; However, I wondered why the macro string ME(t-1) is not resolved properly inside the double-double quotes, unlike inside the single-double quotes %sysfunc(ifc("&i"="MKT",i,j i)); .

Junyong · ‎09-07-2021

May I ask one more? Sorry to bother you. The following code downloads an Excel file of multiple sheets and imports the sheets. filename i temp; proc http url="https://images.aqr.com/-/media/AQR/Documents/Insights/Data-Sets/Quality-Minus-Junk-Factors-Monthly.xlsx" out=i; run; %macro i(i); proc import file=i dbms=xlsx replace out=i; range="&i$A19:%sysfunc(ifc(""&i""=""RF"",B,AD))0"; run; data i; i=put("&i",$11.); set i; where date>.; run; data j; set %sysfunc(ifc("&i"="MKT",i,j i)); run; %mend; proc iml; if exist("j") then call delete("j"); quit; %i(MKT) %i(QMJ Factors) %i(ME(t-1)) %i(RF) The names of the sheets are, for example, MKT , QMJ Factors , ME(t-1) , and RF . Because RF has only two columns, I adjusted range for proc import using %sysfunc(ifc(""&i""=""RF"",B,AD)) —accordingly, I put double quotes "" inside single quotes " . Unfortunately, this adjustment caused an error for ME(t-1) . Can't I use the double quotes with the parentheses together?

Junyong · ‎09-06-2021

It seems adding quotes works as follows. %macro example(argument); %put %sysfunc(ifc("&argument"="string without parenthesis",parenthesis no,parentheses yes)); %mend; /*this works fine*/ %example(string without parenthesis) /*this works as well*/ %example(string with (parentheses))

Junyong · ‎09-06-2021

The following %example is supposed to return parenthesis no if there is no parenthesis in the argument and parentheses yes otherwise. %macro example(argument); %put %sysfunc(ifc(&argument=string without parenthesis,parenthesis no,parentheses yes)); %mend; /*this works fine*/ %example(string without parenthesis) /*this does not work*/ %example(string with (parentheses)) I also tried %example(%str(string with (parentheses))) for the last one, but it didn't work as well. How should I give the argument of parentheses correctly?

Junyong · ‎05-22-2021

SGPLOT Procedure BLOCK Statement shows the following image with blocks around dots—for example, the observation for Janet is located in the middle of the second red box. I use both SERIES and BLOCK as follows—as a working example, 12 observations from January to December 2020 are displayed. A dummy variable equals one only in February and zero otherwise. The outcome, unlike the plot above, draws the February block after the second dot. As in the first, I want the block in the middle rather than on the right of the dot. In this case, how can I relocate the block? The following is the MWE code. data i; do i=1 to 12; t=mdy(i,1,2020); x+rannor(1); y=i=2; output; end; run; data j; input id :$ value fillcolor :$ filltransparency @@; cards; i 0 black 1 i 1 black 0.9 ; ods results=off; ods listing gpath="!userprofile\desktop"; ods graphics/reset; proc sgplot data=i dattrmap=j; series x=t y=x; block x=t block=y/attrid=i nooutline novalues; xaxis valuesformat=yymmddn6.; quit; ods graphics/reset; ods listing gpath=none; ods results=on;

Junyong · ‎03-19-2021

Can't I INFILE multiple CSVs in a ZIP without unzipping them?

Junyong · ‎03-19-2021

FILEVAR allows to INFILE multiple CSVs with the same structure. How can I use this if the CSVs are in FILENAME ZIP? For example, filename i "wid_all_data.zip"; proc http url="https://wid.world/bulk_download/wid_all_data.zip" out=i; run; filename i zip "wid_all_data.zip"; data i; i=dopen("i"); do j=1 to dnum(i); k=cats('i("',dread(i,j),'")'); output; end; run; data j; set i; infile l filevar=k dlm=";" firstobs=2; input (country variable percentile)(:$16.) year value age pop :$16.; run; SAS does not use k=i("WID_data_AN.csv") as FILEVAR here.

Junyong · ‎03-07-2021

The following uses a data set HAVE to create three variables JACK JAMES JOHN in a data set WANT. data have; input name $; cards; Jack James John ; data want; run; data _null_; set have; call execute("data want;set want;"||name||"=1;run;"); run; So, this is equivalent to data want; run; data want; set want; Jack=1; run; data want; set want; James=1; run; data want; set want; John=1; run; But, can I iterate one specific row via CALL EXECUTE using the data as follows? data want; Jack=1; James=1; John=1; run;

Junyong · ‎03-07-2021

Datasets i and j have the labels and the data as follows. filename i temp; proc http url="http://faculty.tuck.dartmouth.edu/images/uploads/faculty/ rafael-laporta/Law_Fin.xls" out=i; run; proc import file=i dbms=xls replace out=i; sheet="definitions"; getnames=no; run; proc import file=i dbms=xls replace out=j; sheet="data"; run; The labels contain commas, percent signs, and unclosed parentheses as follows. How can I label the data correctly? The following macro incorrectly scans the macro strings due to empty spaces. %macro i; proc sql noprint; select a,d into :i separated by "\",:j separated by "\" from i; quit; data j; set j; %do k=4 %to %sysfunc(countw(&i,\)); label %scan(&i,&k,\)="%scan(%nrbquote(&j),&k,\)"; %end; run; %mend; %i Thanks for help.

Junyong · ‎02-06-2021

Re: Need alternative of "Filename" statement with Pipe operator for SAS University Edition and Re: Bulk Download all my files from SAS Ondemand introduce the way to read filenames recursively via modify , replace , and dread without pipe . data i; length i j $800; i='!USERPROFILE\Desktop'; run; data i; modify i; a=filename("i",catx("\",i,j)); a=dopen("i"); if a then do; i=catx("\",i,j); j=""; end; replace; if a; do k=1 to dnum(a); j=dread(a,k); output; end; a=dclose(a); a=filename("i"); run; Usually, I use neither modify nor replace , but why can the code above read filenames recursively while the code below cannot? data i; length i j $800; i='!USERPROFILE\Desktop'; a=filename("i",catx("\",i,j)); a=dopen("i"); if a then do; i=catx("\",i,j); j=""; end; if a; do k=1 to dnum(a); j=dread(a,k); output; end; a=dclose(a); a=filename("i"); run; I wonder whether the two separate data steps are necessary.

Junyong · ‎02-05-2021

Thanks—as mentioned, How to: List all the files in a folder and also Bulk Download all my files from SAS Ondemand answer this question.

Online Status	Offline
Date Last Visited	‎05-05-2024 01:30 AM

Re: How to Prevent Resolution of Ampersand?

How to Prevent Resolution of Ampersand?

How to Italicize Just One Word in FOOTNOTE?

Re: Applying Arrow Tips to SGPLOT Lines and Axes

Applying Arrow Tips to SGPLOT Lines and Axes

Displaying Values for Histograms

SGPLOT VBAR XAXIS Label Interval?

Reading Tab-Delimited Data with Spaces

FILENAME FTP PROMPT in SAS OnDemand

Re: What Unconditional Variance Does AUTOREG Use?

Re: In VIEWTABLE, How Can I Directly Go to Certain Observation?

In VIEWTABLE, How Can I Directly Go to Certain Observation?

DO Loop and INFILE FILEVAR Together

Re: How to Download a Folder from SAS OnDemand?

Re: Skipping Invalid Lines

Re: Extracting JSON in HTML: What If LRECL>32767?

Re: Extracting JSON in HTML: What If LRECL>32767?

Extracting JSON in HTML: What If LRECL>32767?

Re: IFC Evaluation in SAS/MACRO of a String with Parentheses

Re: IFC Evaluation in SAS/MACRO of a String with Parentheses

Re: IFC Evaluation in SAS/MACRO of a String with Parentheses

Re: IFC Evaluation in SAS/MACRO of a String with Parentheses

IFC Evaluation in SAS/MACRO of a String with Parentheses

How to BLOCK Correctly in SGPLOT SERIES?

Re: Multiple CSVs in FILENAME ZIP via INFILE FILEVAR?

Multiple CSVs in FILENAME ZIP via INFILE FILEVAR?

Iterating Specific Rows in CALL EXECUTE

Labelling Using Another Dataset

How Can MODIFY and REPLACE Read Filenames Recursively?

Re: How to Download a Folder from SAS OnDemand?