About hbi

hbi · ‎10-06-2015

Below is a solution that uses a combination of a PROC SQL statement and a data step that contains a do loop. Enjoy. /* intermediate table - pivot "Denied" rows into columns */ PROC SQL; CREATE TABLE side_by_side AS SELECT code , MAX(CASE WHEN status='Paid' THEN status ELSE "" END) AS PaidStatus , SUM(CASE WHEN status='Paid' THEN PaidAmount ELSE . END) AS PaidAmount , MAX(CASE WHEN status='Denied' THEN status ELSE "" END) AS DeniedStatus , SUM(CASE WHEN status='Denied' THEN PaidAmount ELSE . END) AS DeniedAmount FROM Have GROUP BY Code ORDER BY PaidAmount DESC, code; QUIT; DATA want(DROP=PaidStatus DeniedStatus DeniedAmount); ATTRIB Code Status PaidAmount label=''; SET side_by_side; ARRAY _statusArray PaidStatus DeniedStatus; DO OVER _statusArray; Status = _statusArray; IF Status = 'Denied' THEN DO; PaidAmount = DeniedAmount; END; IF NOT MISSING(Status) THEN OUTPUT; END; RUN;

hbi · ‎10-05-2015

You are right ... the boolean statement student in ('A' 'B' 'C') evaluates to 1; thus, for student 'C', 1*10 = 10. In short, the sum operator (+) used in conjunction with the product of a boolean result would result in a non-missing value.

hbi · ‎10-05-2015

Are you trying to parse the URL to extract specific key-value pairs? I looked at the URL string that you provided, and the parameters Title, TestColumn1, TestColumn2, Category, Item Type and Path are not among the parameters in the URL. Or perhaps you are trying to have SAS act as the "user agent" that sends HTTP "get" requests back and forth to the Sharepoint site, in which case, your objective is to scrape web page output? This might be technically challenging if there are permissions on your Sharepoint site and/or if SAS is running on a non-Windows box. One of the reasons for this is that SAS would be an "agent" that is acting on one's behalf, and the default behavior is that SAS does not run under the same context as you. (I think my comment may apply if you are using SAS EG; I am not sure if it applies to PC SAS as I have not used the latter in a number of years). -- hbi

hbi · ‎10-05-2015

Here is a slight variation of Mike's example. This one handles missing values. data x; input student $ grade; datalines; A 50 B 60 C . D 33 ; run; data y; set x; if student in ('A','B','C','D') then project2 = sum(grade, 10); run;

hbi · ‎10-05-2015

I am not sure which interpretation is correct, so I have included two versions. If the objective is to do running sums of each numeric variable (individually), grouped by ID, then in theory, something like this could work: DATA have; INPUT id Numeric1 Numeric2 Numeric3 Numeric4 Character $; DATALINES; 1 1 11 115 1199 xxx 1 2 22 225 2299 xxx 2 3 33 335 3399 xyz 2 4 44 445 4499 xyz 3 5 55 555 5599 zzz 3 6 66 665 6699 zzz ; RUN; /* untested code */ DATA want; SET have; BY id; /* assumes your data is sorted properly */ LENGTH Sum1-Sum4 8; ARRAY num_array Numeric1-Numeric4; ARRAY sum_array Sum1-Sum4; DO OVER num_array; IF first.id THEN sum_array = num_array; ELSE sum_array + num_array; END; RUN; If the objective is to lump all numeric variables together (with the exception of ID) and find their running sum, something like this could work: /* untested code */ DATA want; SET have; BY id; /* id is numeric and it will be summed; thus negate it ... */ obs_total = SUM(of _numeric_) - id; IF first.id THEN running_total = obs_total; ELSE running_total + obs_total; RUN;

hbi · ‎10-05-2015

This version is just for fun. It creates a hash for each pair of IDs. This version is only useful if you want a count of the number of distinct ID pairs. PROC SQL; /* create unique MD5 hash based on ID pair */ CREATE TABLE id_pairs_hashed AS SELECT CASE WHEN id1 < id2 THEN PUT(MD5(CATS(id1, "|", id2)), $hex32.) ELSE PUT(MD5(CATS(id2, "|", id1)), $hex32.) END AS id_pair_md5 length=32 FROM have; QUIT; PROC SQL; SELECT COUNT(DISTINCT id_pair_md5) AS num_distinct_obs FROM id_pairs_hashed; QUIT;

hbi · ‎10-05-2015

[edited to change MAX() statement to equality test within CASE WHEN ... ] Hi, This PROC SQL solution works well with pairs, since a pair of values will always have a minimum and maximum. If one were dealing with n-tuples (id1, id2, id3, etc.), then a different solution would be needed. -- hbi PROC SQL; CREATE TABLE want AS SELECT DISTINCT CASE WHEN id1 <= id2 THEN id1 ELSE id2 END AS id1_fixed , CASE WHEN id1 < id2 THEN id2 ELSE id1 END AS id2_fixed FROM have; QUIT; PROC SQL; SELECT COUNT(*) AS num_distinct_obs FROM want; QUIT;

hbi · ‎09-24-2015

Do you use Enterprise Guide or Regular SAS? This article ( http://support.sas.com/kb/31/715.html ), though slightly dated, states that in the absence of a hotfix, "DBMS=EXCEL" should be used instead of "DBMS=XLS". Also, are you using "OPTIONS COMPRESS=YES;"? This option is absolutely critical when you have a significant number of character variables that vary in length. Permutations of DBMS options (EXCEL/XLS/XLSX) and file formats (.xls, .xlsx, .xml) might be worth a shot (or you can come up with your own): try DBMS=EXCEL with your existing ".xls" file try DBMS=XLSX with your existing ".xls" file within Excel, save the file as ".xlsx" and try DBMS=XLSX within Excel, save the file as Excel ".xml" and try DBMS=XLSX within Excel, save the file as Excel ".xml" and try DBMS=XLS Some of the above pairs may not make sense, so my suggestions should be taken with a grain of salt. -- hbi

hbi · ‎09-23-2015

Something like this should work. It was unclear from your example what filtering criteria should be used for good IDs (that transferred) and bad IDs (that did not transfer), so this example is generic. PROC SQL; SELECT DISTINCT id INTO :good_id_list SEPARATED BY ", " FROM work.my_dataset1 WHERE /* add filtering criteria for IDs that are good (transferred) */ ; QUIT; PROC SQL; SELECT DISTINCT id INTO :bad_id_list SEPARATED BY ", " FROM work.my_dataset2 WHERE /* add filtering criteria for IDs that are bad (did not transfer) */ ; QUIT; DATA _null_; file sendit email from="your_name@name_of_company.com" to=("recipient_name@name_of_company.com") cc=("courtesy_copy_name1@name_of_company.com" "courtesy_copy_name2@name_of_company.com" "courtesy_copy_name3@name_of_company.com") subject="&env. Records Transferred on %sysfunc(date(), yymmdd10.)" importance="High"; if length(&bad_id_list) > 65500 then do; put "BEWARE: macro variable 'bad_id_list' exceeds maximum length of 64 kilobytes."; put "The list of IDs have been truncated."; end; if length(&good_id_list) > 65500 then do; put "BEWARE: macro variable 'good_id_list' exceeds maximum length of 64 kilobytes"; put "The list of IDs have been truncated."; end; put "Below is a list of IDs that transfered:"; put "======================================="; put &good_id_list; put ""; put ""; put "Below is a list of IDs that did not transfer:"; put "============================================="; put &bad_id_list; put ""; put ""; put "Regards,"; put "SAS guy"; put; RUN;

hbi · ‎09-23-2015

Yes RW9, very true. I posted on a different thread ( https://communities.sas.com/t5/Base-SAS-Programming/Problem-with-PROC-SQL-UPDATE/m-p/225920#U225920 ) that had a related problem, but the problem appeared to be magnified because the same dataset was being referenced in both the "set" clause and the "where" clause. -- hbi

hbi · ‎09-22-2015

I do not think the "do" loop is necessary. I have changed your example slightly to replace " " with "_" which will avoid the leading space and trailing space problem. DATA test; format e_STATSLINKAGE $8.; STATSLINKAGE="ABCDE FG"; if index(STATSLINKAGE, "_") > 0 then do; put "ERROR: sorry ... bad replacement character found in input data"; abort; end; /* change space to underscore */ e_STATSLINKAGE=translate(STATSLINKAGE, 'HIJKLMNOPQRSTUVWXYZABCDEFG4567890123_', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 '); RUN; There is another way. If you do not have a need to ever decrypt" the data back to its original value, you could do an MD5 hash. DATA test2; fake_ssn = "000000000"; output; fake_ssn = "111111111"; output; fake_ssn = "999999999"; output; RUN; /* note that if you change your primary key to an MD5, it is a one-way hash, and there is no way to decrypt it and convert it back to its original value */ DATA test2_hashed; SET test2; length ssn_hashed $40; ssn_hashed = put(md5(fake_ssn),$hex32.); RUN;

hbi · ‎09-22-2015

This solution is very similar to Tom's SQL solution, but it uses a subselect within a select. DATA have; length Year 8 Company $8 Failure 8; input Year Company $ Failure; datalines; 1991 A 1 1992 A 1 1993 A 1 2000 A 1 ; RUN; PROC SQL; CREATE TABLE want AS SELECT Year , Company , Failure , (SELECT COALESCE(SUM(Failure), 0) FROM have AS B WHERE A.Company = B.Company AND A.Year - B.Year BETWEEN 1 AND 7) AS Count_In_7_Years FROM have AS A; QUIT; A lookup within an update is possible, but you need to add your new field first using an ALTER TABLE statement. If you run the update statement as-is, you will get an error message that I have pasted below. You would have to uncomment the highlighted line to suppress the error. PROC SQL; ALTER TABLE have ADD Count_In_7_Years num; QUIT; /* uncomment the next line to suppress the error */ /* OPTIONS SQLUNDOPOLICY=NONE; */ PROC SQL; UPDATE have AS A SET Count_In_7_Years = (SELECT COALESCE(SUM(Failure), 0) FROM have AS B WHERE A.Company = B.Company AND A.Year - B.Year BETWEEN 1 AND 7); QUIT; WARNING: A value expression of the SET clause references the data set being updated. ERROR: You cannot reopen WORK.HAVE.DATA for update access with member-level control because WORK.HAVE.DATA is in use by you in resource environment SQL (2). ERROR: PROC SQL could not undo this statement if an ERROR were to happen as it could not obtain exclusive access to the data set. This statement will not execute as the SQL option UNDO_POLICY=REQUIRED is in effect.

hbi · ‎09-22-2015

Hi, This sounds like a mysterious problem. My first thought is that you might consider tweaking "insertbuff" and "dbcommit" parameters to a tiny value (see tests 1, 2 and 3). This is not a good long-term solution (because it hurts performance), but it might get you closer to pinpointing whether the problem is perhaps due to certain kinds of concurrency. Test 4 (sleeping for 10 seconds) would be an unusual coding technique, but it does address your desire to "pause" the program. /* TEST 1 - decrease insertbuff and dbcommit to a very small value */ LIBNAME MSSQL sqlsvr user=myusr1 password=mypwd1 insertbuff=50 dbcommit=50; PROC SQL; INSERT INTO MSSQL.test_table SELECT * FROM dataset_with_lots_of_rows; SELECT 'test1', COUNT(*) FROM MSSQL.test_table; QUIT; /* TEST 2 - same as test 1, but move the count to a separate PROC SQL statement */ PROC SQL; INSERT INTO MSSQL.test_table SELECT * FROM dataset_with_lots_of_rows; QUIT; PROC SQL; SELECT 'test2', COUNT(*) FROM MSSQL.test_table; QUIT; /* TEST 3 - see if PROC APPEND produces makes a difference */ PROC APPEND BASE=MSSQL.test_table DATA=dataset_with_lots_of_rows; RUN; PROC SQL; SELECT 'test3', COUNT(*) FROM MSSQL.test_table; QUIT; /* TEST 4 - introduce 10 seconds of sleep */ PROC SQL; INSERT INTO MSSQL.test_table SELECT * FROM dataset_with_lots_of_rows; QUIT; DATA work.sleep_briefly(DROP=zzz); %LET the_time = %SYSFUNC(TIME(),TIME.); SYSECHO "Sleeping for 10 seconds at ... &the_time"; zzz = sleep(10,1); /* sleep 10 seconds; 1 means use seconds */ RUN; PROC SQL; SELECT 'test4', COUNT(*) FROM MSSQL.test_table; QUIT; Test 5 is a last resort because committing one row at a time will really hurt performance. Test 6 is something to try if you have pinpointed which of the tests produce desirable results, at which point, you can tweak dbcommit and insertbuff to restore database performance to levels that you can live with. /* TEST 5 - if the problem persists, try even smaller values and add INSERT_SQL=NO */ LIBNAME MSSQL sqlsvr user=myusr1 password=mypwd1 insertbuff=1 dbcommit=1 INSERT_SQL=NO; /* repeat tests 1 to 4 and see what happens */ /* TEST 6 - increase insertbuff and dbcommit values that will restore decent performance and see if problem still persists with larger values; make sure insertbuff is a value less than dbcommit */ LIBNAME MSSQL sqlsvr user=myusr1 password=mypwd1 insertbuff=100 dbcommit=1000; /* repeat tests 1 to 4 and see what happens */ Would love to hear feedback as to whether any of these techniques worked!

hbi · ‎09-21-2015

I think one of the themes of the back-and-forth discussion (among community members) is that SAS is not a substitute for an OLTP (online transaction processing) system. If you have a desire to have PROC SQL behave like an OLTP system, then it might be time to migrate your datasets to a RDBMS like Oracle or Teradata. Though SAS datasets superficially look like RDBMS tables, under the hoods, SAS' treatment of them are very different. (Note that PROC SQL is a much later incarnation of what had been almost the exclusive domain of data step programming for eons). PROC SQL, probably for good reason, does not provide transactional database commands such as "begin trans", "commit trans", "rollback", etc., nor does it log transactions in a separate (DB transaction log) file. Table-locking and record-locking are handled very differently too. Additionally, parameters such as "fill factor", "percent free", etc. that are commonplace in the RDBMS world, do not have an analogue with SAS datasets. A PROC SQL update must operate within these confines and still stay true to the fact that it is manipulating a bona fide dataset, rather than a table. The lack of empty space between records, means that everything that follows the updated observation [let's say a given field goes from varchar(1) to varchar(20)], likely requires (though don't quote me on this) that everything else be shifted downwards to ensure that each observation's sort position remains unchanged.

hbi · ‎09-17-2015

I believe that the processing time is high due to zipcode_mod being referenced twice in the same update statement. In your example, it is being used in both the "set" clause (to return a value) and again in the "where" clause (to limit the number of observations that are updated). I do not know the exact impact (in terms of processing time), but it may differ from let's say, referencing the same dataset twice in a set clause; or referencing the same dataset twice in a where clause. Having said that, ensuring that zipcode_mod is used only once in the update statement, may be worth a shot; create a copy of and see if it reduces processing time. I think test 3 would still likely be a clear winner over test 5. /* TEST 5: create a copy of zipcode_mod and call it zipcode_mod_copy */ proc sql; create table zipcode_mod_copy AS select * from zipcode_mod; update zipcode as A set alias_city = (select alias_city from zipcode_mod as n where A.zip=n.zip) where A.zip in (select zip from zipcode_mod_copy); quit; I did not mention this in my previous post, but beware that the macro variable created in test 3 should not exceed 1,000 data values, since that is supposed to be the upper limit of SQL "in" clauses. The version of SAS that I am using allows me to exceed 1,000 data values, but SQL standards generally prohibit list items that exceed the 1,000 cap. proc sql; SELECT zip INTO :list_of_zip SEPARATED BY ", " FROM zipcode_mod; SELECT count(zip) INTO :count_zip FROM zipcode_mod; quit; data _null_; if &count_zip > 1000 then abort abend; run;

Online Status	Offline
Date Last Visited	‎05-06-2016 12:04 PM

Re: Variables Using Count

Re: Character Value in Macro variable

Re: Comparing Two strings

Re: long to wide

Re: Unexplained date error using date macro

Re: Compare two columns first alphabetically?

Re: if and where statement for _n_

Re: Array

Re: Restructure dataset

Re: assign the closet value

Re: For each loop on each returned observation

Re: Fill in the missing values with the previous values

Re: How Can i Multiply the Number of the Columns

Re: Random Forests in Enterprise Miner

Re: Proc SQL merge and find mismatch

Re: proc sql full outer join multiple tables with missing values

Re: Unexplained date error using date macro

Re: if and where statement for _n_

Re: Overwriting of a row in a data set by another row in another data ...

Re: SQL Order By Statement

Re: Can you help me please immediately

Re: Load sharepoint URL with XML into SAS using Base SAS

Re: Can you help me please immediately

Re: Sum all numeric variables

Re: Identifying duplicated of pair of id

Re: Identifying duplicated of pair of id

Re: ERROR: Not enough memory available to allocate storage.

Re: Email ID's of 2 datasets in Email Body

Re: PROC SQL sum where

Re: encryption of primary key using replace function - how about white...

Re: PROC SQL sum where

Re: Capturing Counts from destination SQL Server Database

Re: Problem with PROC SQL UPDATE

Re: Problem with PROC SQL UPDATE