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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.