How to generate a new variable with information from another variable?

[marysmith]

Libname saspms "C:\SAS_PMS";

PROC IMPORT OUT= SASPMS.DATENSATZ_PMS 
            DATAFILE= "C:\SAS_PMS\Datensatz_PMS.xls" 
            DBMS=EXCELCS REPLACE;
     RANGE="Sheet1$"; 
     SCANTEXT=YES;
     USEDATE=YES;
     SCANTIME=YES;
RUN;

Proc print
data = saspms.datensatz_pms;
run;


data saspms.datensatz_pms;
set saspms.datensatz_pms (keep=NIS_Nummer Art_der_Anzeige notificationdate Medicinalproduct StartingDate EndingDate Numberofpatients Numberofphysicians);
run;
Proc print
data = saspms.datensatz_pms;
run;
Proc contents
data = saspms.datensatz_pms;
run;


data saspms.datensatz_pms;
set saspms.datensatz_pms;
Sdate = input(startingdate,ddmmyy10.);
Edate= input(endingdate,ddmmyy10.);
run;

data saspms.datensatz_pms;
set saspms.datensatz_pms;
duration = Edate-Sdate;
label duration= "Duration of study(days)";
run;

proc print 
data= saspms.datensatz_pms;
run;

data saspms.datensatz_pms;
	set saspms.datensatz_pms;
	startpos = prxmatch('[[A-Z]{1}[0-9]{2}[A-Z]{2}[0-9]{2}]', medicinalproduct);
	atc= substr(medicinalproduct, startpos, 7);
run;

proc print 
data= saspms.datensatz_pms;
run;

proc print
data = saspms.datensatz_pms;
var Nis_Nummer atc;
run;

proc print
data= saspms.datensatz_pms;
run;

data saspms.datensatz_pms;
set saspms.datensatz_pms; /*ATC Codes aus Variable=Medicinalproduct und GelbeListe.de*/
if NIS_Nummer = 137 then atc= 'L04AX05'; /* Esbriet, Wirkstoff Pirfenidon, in Datensatz L04AX04 und L04AX05*/
if NIS_Nummer = 146 then atc= '.'; /*Abschlussbericht Roche*/
if NIS_Nummer = 184 then atc= 'M03AX21'; /*Änderungsanzeige, Dysport 500*/
if NIS_Nummer = 185 then atc= 'R03'; /* Änderungsanzeige: jegliche zugelassene Asthmatherapie, R03= Mittel bei obstruktiven Atemwegserkrankungen*/
if NIS_Nummer = 228 then atc= '.'; /*Änderungsanzeige und Beendigung: keine Info*/
if NIS_Nummer = 297 then atc= '.'; /*Änderungsanzeige und Beendigung: keine Info*/
if NIS_Nummer = 1322 then atc= 'V04CX'; /*Hexvix, V04CX: andere Diagnostika*/
if NIS_Nummer = 1387 then atc= '.'; /* Änderungsanzeige & Abschlussbericht: keine Info*/
if NIS_Nummer = 1389 then atc= 'J05AE11'; /*Incivo*/
if NIS_Nummer = 1402 then atc= 'D06BX02'; /*Beendigung: Picato 500*/
if NIS_Nummer = 1405 then atc= 'V60B'; /* Gentiana Magen Globuli, Anthroposophika*/
if NIS_Nummer = 1469 then atc= 'B03AC06'; /*Abschlussbericht: Monofer100mg*/
if NIS_Nummer = 1564 then atc= 'M05BA04 M05BA06 M05BA07 M05BA08'; /*Änderungsanzeige: Bisophosphonate:Alendronat,Ibandronat,Risedronat,Zoledronat*/
if NIS_Nummer = 1681 then atc= 'A03AB07'; /*Vagantin Riemser*/
if NIS_Nummer = 1725 then atc= 'V60B'; /*Apis/Belladonna cum Mecurio, Globuli*/
if NIS_Nummer = 1769 then atc= 'A09A'; /*A09A Melissengeist*/
if NIS_Nummer = 1839 then atc= 'M02AB'; /* M02AB Wärmepflaster*/
if NIS_Nummer = 1906 then atc= 'L01BC'; /* L01BC Gemcitabin*/
if NIS_Nummer = 2067 then atc= 'N06AX22'; /*Valdoxan*/
if NIS_Nummer = 2075 then atc= 'L04AA'; /* L04AA Selektive Immunsuppressiva Teriflunomid*/
if NIS_Nummer = 2087 then atc= 'L01'; /* L01 Antineoplastisches Mittel*/
if NIS_Nummer = 2155 then atc= 'L01BC73'; /*= Teysuno(3 Wirkstoffe: Tegafur, Gimeracil, oteracil-kalium)L01BC53=Tegafur,*/
if NIS_Nummer = 2188 then atc= '.'; /* Keine Info zu Medikament*/
if NIS_Nummer = 2309 then atc= 'A03AP30'; /*Gastritol*/
if NIS_Nummer = 2310 then atc= 'C04AH20'; /*Cefadysbasin, in Excel unter V60 Homöopathika*/
if NIS_Nummer = 2467 then atc= 'A08AH02';/*Cefamagar*/
if NIS_Nummer = 2590 then atc= '.';/*keine Info*/
if NIS_Nummer = 2980 then atc= 'N07XH20';/* Neurodoron*/
if NIS_Nummer = 3186 then atc= 'V60A';/* Meditonsin*/
if NIS_Nummer = 3203 then atc= 'A10A';/* Insuline und Analoga*/
if NIS_Nummer = 3377 then atc= 'B02AA02'; /* Tranexamsäure Antifibrinolyrika*/
if NIS_Nummer = 3431 then atc= '.';  /* keine Info*/
if NIS_Nummer = 3432 then atc= 'G03BA03'; /* Testogel*/
if NIS_Nummer = 3622 then atc= 'L01BC05'; /* Gemcitabine*/
if NIS_Nummer = 4685 then atc= 'R05CA13'; /*Cineol, RCA25 gibt es nicht*/
if NIS_Nummer = 4734 then atc= 'V60B'; /* Anconitum*/
if NIS_Nummer = 5080 then atc= '.'; /* keine Info*/
if NIS_Nummer = 5584 then atc= '.'; /*keine Info*/    
if NIS_Nummer = 5605 then atc= 'L02AE02'; /*Eligard Leuprorelin*/
if NIS_Nummer = 6417 then atc= 'R03DX50' ; /* Antiastmathika*/
if NIS_Nummer = 6471 then atc= 'R05CP'; /* Gelo Myrthol*/
if NIS_Nummer = 6472 then atc= 'R05'; /* Husten und Erkältungsmittel*/
if NIS_Nummer = 6507 then atc= '.'; /*Phytopharmakon Naturreiner heilpflanzensaft, kein ATC*/
if NIS_Nummer = 6625 then atc= 'L03AB L04AA';/*Interferone & selektive Immunsuppressiva*/
if NIS_Nummer = 6640 then atc= 'N04BD';  /* Xadago*/
if NIS_Nummer = 6652 then atc= '.' ;  /* keine Information*/
if NIS_Nummer = 6659 then atc= 'A02AX' ;  /* Adolf Justus Luvos Heilerde*/
if NIS_Nummer = 6666 then atc= '.' ; /*keine Info*/
if NIS_Nummer = 6669 then atc= 'M02AH20' ; /*Aconit Schmerzöl*/
if NIS_Nummer = 6677 then atc= '.' ; /*keine Info*/
if NIS_Nummer = 6682 then atc= 'V60A' ; /*keine Info*/
if NIS_Nummer = 6696 then atc= 'C03XA01' ; /* Jinarc Tolvaptan*/
if NIS_Nummer = 6698 then atc= 'B02BX05' ; /* Revolade*/
if NIS_Nummer = 6703 then atc= 'J05AX67 J05AX16'; /* Viekirax und Exviera*/
if NIS_Nummer = 6719 then atc= 'J05A'; /* direkt wirkende antivirale Mitel*/
if NIS_Nummer = 6720 then atc= '.'; /* keine info*/
if NIS_Nummer = 6735 then atc= 'V60A'; /* Homöopathika*/
if NIS_Nummer = 6736 then atc= 'C02AB C09A C09C C08 C07A'; /*C02AB Methyldopa,C09A ACE-HEMMER, C09C ANGIOTENSIN-II-ANTAGONISTEN,C08 CALCIUMKANALBLOCKER,C07A BETA-ADRENOZEPTOR-ANTAGONISTEN*/
if NIS_Nummer = 6815 then atc= '.'; /*keine Info*/
if NIS_Nummer = 6816 then atc= 'L01CD02'; /* Docetaxal ORCA zwei*/
if NIS_Nummer = 6857 then atc= '.'; /* keine Info*/
if NIS_Nummer = 6858 then atc= '.'; /* keine Info*/
if NIS_Nummer = 6862 then atc= 'R05X'; /* Esberitox*/
if NIS_Nummer = 6876 then atc= '.'; /* keine Info*/
if NIS_Nummer = 6877 then atc= '.'; /* keine Info*/
if NIS_Nummer = 6878 then atc= 'C02 C03 C07 C08 C09 C10'; /*C02Antihypertonika; C03Diuretika;C07Beta-Adrenorezeptor-Antagonisten;C08Calciumkanalblocker; C09 Mittel mit Wirkung auf das Renin-Angiotensin-System ;C10Lipidsenkende Mittel*/
if NIS_Nummer = 6884 then atc= '.'; /*keine Info*/
if NIS_Nummer = 7094 then atc= '.'; /* frei verkäufliche Schmerzmedikation*/
if NIS_Nummer = 7096 then atc= 'J05A'; /* direkt wirkende antivirale Mittel*/
if NIS_Nummer = 7098 then atc= 'A16AX14'; /* Galafold Hartkapseln*/
if NIS_Nummer = 7102 then atc= 'J05'; /* Antivirale Mittel zur systemischen Anwendung*/
run;

data saspms.datensatz_pms;
set saspms.datensatz_pms;
if NIS_Nummer < 8 then delete;
run;


proc print
data = saspms.datensatz_pms;
var atc;
where atc =.;
run;

Dear community,

I have a Variable ATC-Code, which has 7 characters and always starts with a letter, then 2 numbers, 2 letters, 2 numbers (for example: B03XA01). Unfortuantelly some of those ATC Codes are not complete(e.g. V60A). I want SAS to show me those cases where the ATC Variable has less than 7 characters or has a missing.

How can I do it? does anybosy know?

Not even this worked 😞

proc print
data = saspms.datensatz_pms;
var atc;

[RW9]

proc print data=have;
  where lengthn(atc) < 7;
run;

The lengthn() function returns the length of a string with spaces from start/end removed.  Maybe use more concise examples in future as thats a long wall of text.

[PaigeMiller · Accepted solution]

To find places where the ATC code is less than the full number of characters, try this:

data want;
    set have;
    len=length(atc);
    if len<7 then output;
run;

[Astounding]

The simplest method:

proc freq data=saspms.datensatz_pms;

tables atc / missing;

where length(atc) < 7;

run;

However, there are several complications that would require more programming.

First, the length of ATC is set by the first mention of the variable.  So it would not be long enough to hold a value like

'M05BA04 M05BA06 M05BA07 M05BA08'

You can correct that by adding a statement before the SET statement (then re-running):

data saspms.datensatz_pms;

length atc $ 40;

set saspms.datensatz_pms;

... then the rest of the logic

A second issue:  when you have multiple word values, you might want to check the length of each word within the assigned value.  That also can be done, but takes a little more programming.  So let us know if that's the right direction to take.

[Kurt_Bremser]

Your problems start with the use of an Excel file for data import. The Excel file format is unusable for this, as it forces SAS to make guesses about the data structure that cause inconsistent results.

Export your Excel data to a csv file, and import that. Then use the code (created by proc import) from the log and adapt it to the specifications of the data.

Now you can start dealing with inconsistencies in the values themselves. Simple use of the length() function will find the short values, as already suggested.