How can I obtain IDnumber unique and CostumerNumber if data set is like:
Te problem is CostumerNumbe(num) in some information months is missing and in others not.
The data set has 600 milllons of obs.
Idnumber(char) CostumerNumbe(num) INFORMATION DATE
100 . 31May2013
100 1234 30Jun2013
100 . 31Jul2013
100 1234 31Aug2013
100 . 30Sep2013
100 . 31Oct2013
100 1234 30Nov2013
100 1234 31Dec2013
100 1234 31Jan2014
100 . 28Feb2014
101 . 31May2013
101 6754 30Jun2013
101 6754 31Jul2013
101 . 31Aug2013
101 6754 30Sep2013
101 . 31Oct2013
101 6754 30Nov2013
101 . 31Dec2013
101 6754 31Jan2014
101 . 28Feb2014
The result I want is:
Idnumber(char) CostumerNumbe(num)
100 1234
101 6754
proc sql;
create table want as
select distinct Idnumber, CostumerNumber
from YourDataSet
where not missing(CostumerNumber);
quit;
Not claiming to be the most efficient.
Thank you Ballardw, do you know other way using just sas code? not using proc sql
Proc sql is BASE SAS code, just one procedure among many.
Are you requesting a data step solution? The other relatively easy approaches involve Proc Summary or Proc freq but end up with summary variables that get dropped from the result data set.
proc freq data=yourdata noprint;
where not missing(CostomerNumber);
tables Idnumber* CostumerNumber / list out=want(drop=count percent);
run;
for instance.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.