[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 414
  • Last Modified:

find unique records

Hi,
i have three columns customer_no and cust_name and file_name from where the data comes
i want to run a report for 10 unique customer_no + cust_name and want to display from
which file_name they came from.

Thanks
0
sam2929
Asked:
sam2929
  • 2
  • 2
1 Solution
 
theartfuldazzlerCommented:
Hi

I'm not 100% sure on what your problem is.

1.  If you are trying to find unique rows; then you can use a PROC SORT:

PROC SORT DATA=DSName NODUP;
By Customer_no Cust_name File_name;
RUN;

To display them, you can use PROC PRINT:

PROC PRINT DATA=DSName;
RUN;


2.  Alternatively, you can use ODS and SQL as in the following example:

ODS HTML FILE="c:\docs\Test.html";

PROC SQL;
  select distinct Customer_no, Cust_name, File_name;

ODS HTML CLOSE;

The ODS statement will create a HTML file with a table displaying the unique records.

0
 
sam2929Author Commented:
Ok this is the scenario
We have Wednesday’s file contains data from Monday & Tuesday and Thursday’s file contains data from Tuesday & Wednesday so tuesday’s data is repeated, but may also be new data for tuesday that came in on Thursday’s file. so how we can get rid of duplicated data
0
 
d507201Commented:
I haven't tested this but here's an approach.  Doing it with a SQL join might be more efficient.

proc sort data=MonTuesWed;
  by key;  ** key=custName||custNbr;
run;

proc sort data=TuesWedThurs;
  by key;  ** key=custName||custNbr;
run;

data MonTuesWedThurs;  merge MonTuesWed(in=inMTW) TuesWedThurs(in=inTWT)
  by key;
  if inMTW then output MonTuesWedThurs;  ** this captures data from Mon Tues Wed. ;
  if inTWT and not inMTW then output MonTuesWedThurs;  ** this captures the Thurs data.  ;run;


0
 
sam2929Author Commented:
quick question how can i do  quick frequencey for nulls or missing data
i run this i get results what this shows?
proc freq data=oaa.tmpo ;
     table no / norow nocol nopercent;
      run;
0
 
d507201Commented:
The tables statement in proc freq has a missing option that will treat blank/null/missing as a valid value.

proc freq data=oaa.tmpo ;
     table no / norow nocol nopercent MISSING;
      run;

If you want to see the missing accounts,

** this presumes that variable no is numeric. ;
data missing; set oaa.tmpo(where=(no=.));
run;
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now