Recently in the SAS Community Library: Your often contains the information you need, but not sequenced in the order required for processing. @SASJedi shows you how to properly sequence data so you can compare the data in one table to the data in another, conduct merges or joins and more.
Hi Need help to Highlight mismatched rows(compare 2-3 , 4-5,6-7 rows so on dynamically) data Using SAS while generating a multi sheet excel file . SAS is on Unix server. I am using SAS EG 7.1 Below is the data I have to compare every two rows and highlight the mismatched data while generating the Excel and send it via email. Note - there are multiple sheets and columns vary (sometimes there maybe 10 columns, sometimes it may be 18) I have SAS code(used ODS) to generate excel with multiple sheets and send it via email and separate a VBA macro to highlight the mismatched data which I run manually. But I am unable to integrate both in to single code so that no manual work is needed, I really appreciate if anyone can suggest any way to do it in one single code. Below is sample dataset , rows and column are dynamically created based on the project. Need to compare 2-3,4-5, 6-7..so-on rows and highlight(I have given blue to the font but i need whole box as yellow) only when they don’t match. ssn firstname lastname empid address city state zip 000-11-1234 abc d 1001 plotno 123 abcd rd 12345 000-11-1234 abc e 1001 plotno 123 abcd rd 12345 111-12-3456 efgh f 2001 house 897 tyui fg 23456-789 111-12-3456 efgh f 2001 hs 897 tyui fg 23456 444-56-7890 wrt y 3001 xc 12345 444-56-7890 wert y 3002 xc 12345 my VBA code: Sub compare_Auto() Dim LastColumn As Long LastColumn = ActiveSheet.Range("A1").CurrentRegion.Columns.Count Dim rng As Range, rngDiff As Range Set rng = Range(Cells(2, 7), Cells(3, LastColumn)) Do While Application.CountA(rng) > 0 'loop while have content Set rngDiff = Nothing 'reset range On Error Resume Next 'ignore "No cells were found" error if no differences Set rngDiff = rng.ColumnDifferences(Comparison:=rng.Cells(1)) On Error GoTo 0 'stop ignoring errors If Not rngDiff Is Nothing Then rngDiff.Interior.ColorIndex = 6 'color all differences rngDiff.Offset(-1).Interior.ColorIndex = 6 End If Set rng = rng.Offset(2) 'two rows down Loop End Sub
... View more
Hi folks, I have been following the SAS documentation on this link , especially example 6 to the below code: filename output sftp lsa
host="sftphost"
optionsx='pw "somepw"' user="userid" debug;
data temp;
infile output truncover;
input a $200.;;
run; Basically, I am just trying to connect to the server and list all the files under the working directory, but I got the error message saying "ERROR: Directory or file ls -a w "somepw" userid@sftphost doesn't exist.". So, what am I missing here?
... View more
I am using PRXMATCH with a Perl regular expression(RegEx) in a WHERE clause in PROC PRINT. The RegEx includes a macro variable containing a drug list and one of these drug names includes a forward slash. I am getting an error even after using a escape character (back slash) in the RegEx. I would appreciate your help resolve the issue. Partial SAS Log: ERROR: Invalid characters "ATROP \//i" after end delimiter "/" of regular expression "/Propantheline|Propoxyphene|Reserpine|Ticlopidine|Trimethobenzamide|DIPHEN/ATROP \//i". * Create a macro variable of study drugs;
%let STUDY_DRUG_LIST = Propantheline|Propoxyphene|Reserpine|Ticlopidine|Trimethobenzamide|DIPHEN/ATROP;
* Create an example data set of prescription fills (patient ID and drug names) ;
Data work.pmed;
input dupersid: $10. rxname $20.;
datalines;
2320038102 NITROFURANTN
2320038102 EZETIMIBE
2320038102 Propantheline
2320040101 Reserpine
2320040101 Reserpine
2320040101 NOVOLOG
2320040101 NOVOLOG
2320040105 DIPHEN/ATROP
2320040105 DIPHEN/ATROP
2320040105 DIPHEN/ATROP
;
* List observations using PRXMATCH with a Perl regular expression in the WHERE clause (Study drug list);
proc print data=work.pmed;
where prxmatch ("/&study_drug_list2 \//i", rxname);
run;
... View more
Iam looking for report like below ,
2nd page always should be signature page like below
By variable is Age
let's say i have 35,40,45 ages so it should be total 6 pages
3 pages of data and 3 pages of signature pages
but 2nd page should be always a signature page like below
Page No :1
Title "Report Page1" by Age 35
Name
Age
Sex
Tom
35
M
Martin
35
M
Sophie
35
F
Page No : 2
I here by confirm ---------Signature above report is correct
Page No : 3
Title "Report Page1" by Age 40
Name
Age
Sex
Stephine
40
M
Don
40
M
Thomas
40
M
Page No : 4
I here by confirm ---------Signature above report is correct
... View more
Iam looking for report like below ,
2nd page always should be signature page like below
By variable is Age
let's say i have 35,40,45 ages so it should be total 6 pages
3 pages of data and 3 pages of signature pages
but 2nd page should be always a signature page like below
Page No :1
Title "Report Page1" by Age 35
Name
Age
Sex
Tom
35
M
Martin
35
M
Sophie
35
F
Page No : 2
I here by confirm ---------Signature above report is correct
Page No : 3
Title "Report Page1" by Age 40
Name
Age
Sex
Stephine
40
M
Don
40
M
Thomas
40
M
Page No : 4
I here by confirm ---------Signature above report is correct
... View more