Specifying method=sas003 will give you 256-bit key plus 16-bit salt AES encoding, totally different from SHA1.  There is not a way (that I am familiar with) to generate a SHA1 hash with PROC PWENCODE.  You can use java, which wouldn't be too complicated.  Made much easier by the fact that PROC GROOVY exists in 9.3+ %let toHash=something to hashify; proc groovy; submit "&toHash."; import java.security.MessageDigest exports.sha1 = new BigInteger(1, MessageDigest.getInstance("SHA1").digest(args[0].getBytes())).toString(16) endsubmit; quit; %put &sha1.; Side-note: You shouldn't post the same question to multiple communities. ... View more

In case it will be helpful to others in the future, here is an example of using ADO to assign a new libref and select from a table within the reference using Powershell. SasAdoDbExample.ps1 or PHP SasAdoDbExample.php $conn = New-Object -ComObject ADODB.Connection #Connect to a remote server using IOM $conn.Open('Provider=sas.IOMProvider.9.3;User ID=user;Password=pass;Data Source="";SAS Machine DNS Name=workspace.mycompany.com;SAS Port=8591;SAS Protocol=2') #Assign a new libref using ADODB $cmd = New-Object -ComObject ADODB.Command $cmd.ActiveConnection = $conn $cmd.CommandType = adCmdText $cmd.CommandText = 'libname foo "/path/to/something"' $cmd.Execute() #SQL data $recordSet = $conn.Execute("select * from foo.something") #Collect the data from recordSet into rows array $recordSet.MoveFirst() $rows = @() do {    $row = New-Object psobject    for ($i=0; $i -lt $recordSet.Fields.Count; $i++)   {    $row | Add-Member noteproperty `    -name $recordSet.Fields.Item($i).Name `    -value $recordSet.Fields.Item($i).Value;   }    #yield    $rows += $row    $recordSet.MoveNext() } until ($recordSet.EOF -eq $true) #create gridview of data executed in recordSet $rows | Out-GridView $recordSet.Close() $conn.Close() ... View more

, Please review the following.  I included some brief comments to hopefully explain along the way what is happening.  This will process the multiple market_types together.  Feel free to ask any followup questions you may have. data one; infile cards truncover; length Market_type $ 9; input id $ (Q_RATING R_RATING S_RATING) (:$7.); do market_type='Retail', 'Wholesale', 'SME';    output;    end; cards4; 900129 2000425 900135 Baa1  A- 2000328 900152 Baa1 NR A 900265 Ba1 BBB- 900005 Baa1  A+ 900108 Baa2 BBB+ A 900121 A1 A A+ 900008 A2  A+ 6964444 Baa3 BB+ 900149   BBB+ 900124 000100-502 900153 A2  A+ 2000315 900136 Baa1 BBB+ 900122 Aa3 AA- AA- 900155 A3 BBB+ BBB+ 1103127 900185 A1  A+ 900151 Baa1 900172 A2 A- A 900007  A- A 99900096 Aa3 A+ A+ 900216 A2 NR A+ 99900169 Baa1 BBB+ A+ 900016 A1 A- A+ 900385 Aa3 A+ AA- ;;;; data two; infile cards truncover; input Rank (Market_type Rating_LKUP Q_RATING R_RATING S_RATING R_PCT) (:$9.); cards4; 24 Retail UR Unrated Unrated Unrated -% 23 Retail 1 Aaa AAA AAA 20% 22 Retail 2+ Aa1 AA+ AA+ 20% 21 Retail 2 Aa2 AA AA 20% 20 Retail 2- Aa3 AA- AA- 20% 19 Retail 3+ A1 A+ A+ 50% 18 Retail 3 A2 A A 50% 17 Retail 3- A3 A- A- 50% 16 Retail 4+ Baa1 BBB+ BBB+ 100% 15 Retail 4 Baa2 BBB BBB 100% 14 Retail 4- Baa3 BBB- BBB- 100% 13 Retail 5+ Ba1 BB+ BB+ 100% 12 Retail 5 Ba2 BB BB 100% 11 Retail 5- Ba3 BB- BB- 100% 10 Retail 6+ B1 B+ B+ 150% 9 Retail 6 B2 B B 150% 8 Retail 6- B3 B- B- 150% 7 Retail 7+ Caa1 CCC+ CCC+ 150% 6 Retail 7 Caa2 CCC CCC 150% 5 Retail 7- Caa3 CCC- CCC- 150% 4 Retail 8 3 Retail 9 2 Retail 10 24 SME UR Unrated Unrated Unrated -% 23 SME 1 Aaa AAA AAA 30% 22 SME 2+ Aa1 AA+ AA+ 30% 21 SME 2 Aa2 AA AA 30% 20 SME 2- Aa3 AA- AA- 30% 19 SME 3+ A1 A+ A+ 60% 18 SME 3 A2 A A 60% 17 SME 3- A3 A- A- 60% 16 SME 4+ Baa1 BBB+ BBB+ 110% 15 SME 4 Baa2 BBB BBB 110% 14 SME 4- Baa3 BBB- BBB- 110% 13 SME 5+ Ba1 BB+ BB+ 110% 12 SME 5 Ba2 BB BB 110% 11 SME 5- Ba3 BB- BB- 110% 10 SME 6+ B1 B+ B+ 160% 9 SME 6 B2 B B 160% 8 SME 6- B3 B- B- 160% 7 SME 7+ Caa1 CCC+ CCC+ 160% 6 SME 7 Caa2 CCC CCC 160% 5 SME 7- Caa3 CCC- CCC- 160% 4 SME 8 3 SME 9 2 SME 10 24 Wholesale UR Unrated Unrated Unrated -% 23 Wholesale 1 Aaa AAA AAA 10% 22 Wholesale 2+ Aa1 AA+ AA+ 10% 21 Wholesale 2 Aa2 AA AA 10% 20 Wholesale 2- Aa3 AA- AA- 10% 19 Wholesale 3+ A1 A+ A+ 40% 18 Wholesale 3 A2 A A 40% 17 Wholesale 3- A3 A- A- 40% 16 Wholesale 4+ Baa1 BBB+ BBB+ 90% 15 Wholesale 4 Baa2 BBB BBB 90% 14 Wholesale 4- Baa3 BBB- BBB- 90% 13 Wholesale 5+ Ba1 BB+ BB+ 90% 12 Wholesale 5 Ba2 BB BB 90% 11 Wholesale 5- Ba3 BB- BB- 90% 10 Wholesale 6+ B1 B+ B+ 140% 9 Wholesale 6 B2 B B 140% 8 Wholesale 6- B3 B- B- 140% 7 Wholesale 7+ Caa1 CCC+ CCC+ 140% 6 Wholesale 7 Caa2 CCC CCC 140% 5 Wholesale 7- Caa3 CCC- CCC- 140% 4 Wholesale 8 3 Wholesale 9 2 Wholesale 10 ;;;; data flip_two;    set two;    by market_type descending rank;    array r[3] Q_RATING R_RATING S_RATING;    drop i;    do i=1 to dim(r);       rating_type = vname(r);    rating = r;    output;       end;    drop Rating_LKUP Q_RATING R_RATING S_RATING; run; data want;    *initialize variables for hash, does not actually set;    if 0 then set one flip_two;    *define our hash lookup;    *parameters :: market_type, rating_type and rating;    *return     :: rank, r_pct;    declare hash rlkup(dataset:'flip_two');       rlkup.definekey('market_type', 'rating_type', 'rating');    rlkup.definedata('rank', 'r_pct');    rlkup.definedone();    *define hash rates, used for keeping rank and r_pct;    *use ordered ascending to help find middle/min later;    *use multidata to avoid collision on equal ranks for different rating_types;    declare hash rates(ordered:'a', multidata:'y');       rates.definekey('rank');    rates.definedata('r_pct');    rates.definedone();    *use the iterator to move through rates array and find min/middle;    declare hiter iter;    *create loop to process data from ds :: one;    do until(done);       set one end=done;    *this array contains each rating to loop over and lookup rank and r_pct from hash;    array h[3] Q_RATING R_RATING S_RATING;    drop i;    do i=1 to dim(h);       *we manually define keys to get rating_type and rating from h array;       rc = rlkup.find(key: market_type, key: vname(h), key: h);   *add the rank and r_pct to rates hash;   rc = rates.add();       end;       *the number of missing ratings from input dataset :: one;       miss = cmiss(of h );    *if all missing then get Unrated r_pct, you may need to make changes here besed on your criteria;    if miss=3 then rc = rlkup.find(key:market_type, key:'Q_RATING', key:'Unrated');    else do;          *create new iterator object to move through rates hash;          iter = _new_ hiter('rates');   *move through rates hash and find min/middle rank;   *min is first because rates hash sorted 'd';          iter.first();   *middle if miss=0 get next after first of iter;   if miss=0 then iter.next();   *to clear the rates hash we need to delete the iter;          iter.delete();          end;       output;    *clear the hash for next row;    rates.clear();       end;    keep id market_type q_rating r_rating s_rating rank r_pct;    stop; run; ... View more

Charlotte, I posted the sort and choose method without thinking through it.  It would choose missing values frequently when it shouldn't, because the missing values would be sorted to the front.  Tentatively, I will say the following will address the issue: call sortn(of b ); *sort the ratings rank array in ascending order, nulls first; r_pct = put(ifn(nmiss(of b )=0, b[2], min(of b , 23) ), rank2pct.); If the number of missing elements in the array b is zero, then we have a ranking for each rating and we want to select the middle value.  Since we sorted the array, the middle value with be present in the second index of the array b[2] If the number of missing elements is not zero, then we want to take the min value for ranking for ratings in array b, or if all are null, we want to assign 23/UR (this was 24 previously, which equated to missing, this may still not be correct as I am working from memory) I am going to look back over this thread later to make sure. ... View more

Charlotte, While splitting the data apart in this manner is not always the most efficient method, I am glad to hear that you found a solution to work for you.  For this 4th criteria you just need a simple logic change.  By taking the b array and sorting it's values you can easily target the min, max or middle value.  You will either want the b[1] to get the min value, or b[2] to get the middle value when the number of missing elements in the b array is zero. call sortn(of b ); r_pct = put(ifn(nmiss(of b )=0, b[2], b[1]), rank2pct.); ... View more

Charlotte, I would challenge you to take one of these examples and attempt to modify it to meet this additional criteria.  The hash method presented by would not be difficult to add additional ratings for with the new Market_type class and the same goes for the method using formats.  The first method I posted would probably be the most complex to add this new functionality to, which, if that is the method you chose, may be why you are finding this difficult. For the hash method you could do something like the following: select(market_type); when ('retail') do; ...get the min rating; end; when ('wholesale') do; ... otherwise; end; You will also want to change from looking from the MIN_RANK to MAX_RANK, if I remember you problem correctly and set the default for MAX_RANK to 1 to get the UR result you want. For modifying the Format method: create the 3 data sets to 9 unique formats add a second dimension to the array fmt, this dimension will be the market_type array fmt[3,0:4] $ 32 _temporary_ ('retail' 'retailQrank.' 'retailRrank.' 'retailStype.' 'retailrank2pct.' ...); and then lookup with help from whichc function b[_n_]=inputn(a[_n_], fmt[whichc(market_type, fmt[1,0], fmt[2,0], fmt[3,0])_n_]); then modify the rank2pct lookup in suit and add logic to set to UR when needed. ... View more

It's not something I'd really recommend, but's it certainly possible to do. ... View more

If you want to use URL then: Get Wget for Windows Get bsdtar for Windows with LibArchive for Windows filename x pipe 'wget -qO- http://donnees.ville.montreal.qc.ca/storage/f/2013-10-05T17%3A49%3A08.194Z/l2p-801-15052013-leplateau-mont-royal.zip | bsdtar -xvf-'; Where: wget downloads the zipfile -q sets the wget command to be quiet -O- sets the output to be directed to STDOUT bsdtar reads the zip file from the pipe and extracts the contents, again to STDOUT -x extract -v tells bsdtar to operate verbosely -f- indicates to operate on the file coming from STDIN ... View more

You said you are wanting to use UNC path, not URL, very different. cmd /c - starts a new instance of the command interpreter and carries out the following command string and stops http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/cmd.mspx?mfr=true pushd \\myshare\folder\unc - assigns a UNC path a temporary drive letter and changes directory to this location https://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/pushd.mspx?mfr=true C:\PROGRA~1\7-Zip\7z.exe x -so foo.zip 2> _garbage.txt - 7-zip is a open source file archiver that has the ability to output the contents of a ZIP archive to STDOUT 7-Zip Where:      x - denotes to eXtract from the archive      -so - write contents of archive to STDOUT      foo.zip - is the name of the file contained in the UNC path assigned with PUSHD      2> _garbage.txt - this redirects the other messages produced by 7z.exe to a junk file, redirecting to NUL doesn't work, for some reason del _garbage.txt - Delete the file we had to create with 7z.exe to get the STDOUT write to work https://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/del.mspx?mfr=true popd - Removes the temporary drive-letter assignment created with pushd and returns to the original working directory https://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/popd.mspx?mfr=true && The double ampersand (&&) command separator strings all these commands together, with error checking (only running one after the previous runs without error http://support.microsoft.com/kb/279253 If you are wanting to use a proper URL in Windows it would be a different set of commands and different tools. ... View more

In windows you could try something like the following: filename x pipe 'cmd /C "pushd \\myshare\folder\unc && C:\PROGRA~1\7-Zip\7z.exe x -so foo.zip 2> _garbage.txt && del _garbage.txt && popd"'; ... View more

You can do it with a pipe (which will be system dependent).  The following would be an example for linux. filename x pipe 'wget -qO- http://donnees.ville.montreal.qc.ca/storage/f/2013-10-05T17%3A49%3A08.194Z/l2p-801-15052013-leplateau-mont-royal.zip | funzip'; ... View more

The format of your input is not very clear.  Please edit your post. ... View more