first dot / last dot list processing help [SAS]

Prev: Quotes in a -SYSPARM value
Next: how to get the code lines into the program editor window?

From: Bill McKirgan on 15 Apr 2010 22:35

I'm stumped, but very close to the solution I see...at least I think
so.

I have a collection of metadata that I am trying to transform into SAS
format statements. The data are nicely organized, coming from an
OpenClinica implementation of CDISC, and I am at the final stages of
telling SAS how to write the lines of code that I want to save to text
files for later use.

The following sample is close to what I want (a series of valid proc
format VALUE statements); however, the closing semicolon IS MISPLACED.
So, here's my code:

data _null_; set code_fmts;

if codelist_ordinal le 2 ; /* testing */
by codelist_ordinal;

if _n_=1 then do;
firstline='proc format ; ';
put firstline;
end;

if first.codelist_ordinal then do;
fmtnameline='value '||sasformatname;
put fmtnameline;
end;

if last.codelist_ordinal then do;
endvalue=';';
put endvalue;
end;

lines= codedvalue || '="' ||trim(translatedtext)||'"';
put lines;
run;

And here's the log...

proc format ;
value YES_NO
1 ="Yes"
0 ="No"
..I ="NI"
..A ="NA"
..U ="UNK"
;
..O ="OTH"
value NEUROPAT
0 ="Grade0"
1 ="Grade1"
2 ="Grade2"
3 ="Grade3"
4 ="Grade4"
..I ="NI"
..A ="NA"
..U ="UNK"
;
..O ="OTH"
NOTE: There were 5025 observations read from the data set
WORK.CODE_FMTS.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

Of course I want the semicolon to FOLLOW the last value in each VALUE
statement.

Can someone suggest a way for me to fix this?

Thank you in advance.

Bill McKirgan

From: Bill McKirgan on 15 Apr 2010 22:40

On Apr 15, 9:35 pm, Bill McKirgan <bill.mckir...(a)gmail.com> wrote:
> I'm stumped, but very close to the solution I see...at least I think
> so.
>
> I have a collection of metadata that I am trying to transform into SAS
> format statements. The data are nicely organized, coming from an
> OpenClinica implementation of CDISC, and I am at the final stages of
> telling SAS how to write the lines of code that I want to save to text
> files for later use.
>
> The following sample is close to what I want (a series of valid proc
> format VALUE statements); however, the closing semicolon IS MISPLACED.
> So, here's my code:
>
> data _null_; set code_fmts;
>
> if codelist_ordinal le 2 ; /* testing */
> by codelist_ordinal;
>
> if _n_=1 then do;
> firstline='proc format ; ';
> put firstline;
> end;
>
> if first.codelist_ordinal then do;
> fmtnameline='value '||sasformatname;
> put fmtnameline;
> end;
>
> if last.codelist_ordinal then do;
> endvalue=';';
> put endvalue;
> end;
>
> lines= codedvalue || '="' ||trim(translatedtext)||'"';
> put lines;
> run;
>
> And here's the log...
>
> proc format ;
> value YES_NO
> 1 ="Yes"
> 0 ="No"
> .I ="NI"
> .A ="NA"
> .U ="UNK"
> ;
> .O ="OTH"
> value NEUROPAT
> 0 ="Grade0"
> 1 ="Grade1"
> 2 ="Grade2"
> 3 ="Grade3"
> 4 ="Grade4"
> .I ="NI"
> .A ="NA"
> .U ="UNK"
> ;
> .O ="OTH"
> NOTE: There were 5025 observations read from the data set
> WORK.CODE_FMTS.
> NOTE: DATA statement used (Total process time):
> real time 0.00 seconds
> cpu time 0.00 seconds
>
> Of course I want the semicolon to FOLLOW the last value in each VALUE
> statement.
>
> Can someone suggest a way for me to fix this?
>
> Thank you in advance.
>
> Bill McKirgan

Doah...nevermind

data _null_; set code_fmts;

if codelist_ordinal le 2 ; /* testing */
by codelist_ordinal;

if _n_=1 then do;
firstline='proc format ; ';
put firstline;
end;

if first.codelist_ordinal then do;
fmtnameline='value '||sasformatname;
put fmtnameline;
end;
/* fixed by putting LINES= here *******/
lines= codedvalue || '="' ||trim(translatedtext)||'"';
put lines;
if last.codelist_ordinal then do;
endvalue=';';
put endvalue;
end;

run;

proc format ;
value YES_NO
1 ="Yes"
0 ="No"
..I ="NI"
..A ="NA"
..U ="UNK"
;
..O ="OTH"
value NEUROPAT
0 ="Grade0"
1 ="Grade1"
2 ="Grade2"
3 ="Grade3"
4 ="Grade4"
..I ="NI"
..A ="NA"
..U ="UNK"
;
..O ="OTH"
NOTE: There were 5025 observations read from the data set
WORK.CODE_FMTS.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

From: Bill McKirgan on 15 Apr 2010 22:42

On Apr 15, 9:40 pm, Bill McKirgan <bill.mckir...(a)gmail.com> wrote:
> On Apr 15, 9:35 pm, Bill McKirgan <bill.mckir...(a)gmail.com> wrote:
>
>
>
>
>
> > I'm stumped, but very close to the solution I see...at least I think
> > so.
>
> > I have a collection of metadata that I am trying to transform into SAS
> > format statements. The data are nicely organized, coming from an
> > OpenClinica implementation of CDISC, and I am at the final stages of
> > telling SAS how to write the lines of code that I want to save to text
> > files for later use.
>
> > The following sample is close to what I want (a series of valid proc
> > format VALUE statements); however, the closing semicolon IS MISPLACED.
> > So, here's my code:
>
> > data _null_; set code_fmts;
>
> > if codelist_ordinal le 2 ; /* testing */
> > by codelist_ordinal;
>
> > if _n_=1 then do;
> > firstline='proc format ; ';
> > put firstline;
> > end;
>
> > if first.codelist_ordinal then do;
> > fmtnameline='value '||sasformatname;
> > put fmtnameline;
> > end;
>
> > if last.codelist_ordinal then do;
> > endvalue=';';
> > put endvalue;
> > end;
>
> > lines= codedvalue || '="' ||trim(translatedtext)||'"';
> > put lines;
> > run;
>
> > And here's the log...
>
> > proc format ;
> > value YES_NO
> > 1 ="Yes"
> > 0 ="No"
> > .I ="NI"
> > .A ="NA"
> > .U ="UNK"
> > ;
> > .O ="OTH"
> > value NEUROPAT
> > 0 ="Grade0"
> > 1 ="Grade1"
> > 2 ="Grade2"
> > 3 ="Grade3"
> > 4 ="Grade4"
> > .I ="NI"
> > .A ="NA"
> > .U ="UNK"
> > ;
> > .O ="OTH"
> > NOTE: There were 5025 observations read from the data set
> > WORK.CODE_FMTS.
> > NOTE: DATA statement used (Total process time):
> > real time 0.00 seconds
> > cpu time 0.00 seconds
>
> > Of course I want the semicolon to FOLLOW the last value in each VALUE
> > statement.
>
> > Can someone suggest a way for me to fix this?
>
> > Thank you in advance.
>
> > Bill McKirgan
>
> Doah...nevermind
>
> data _null_; set code_fmts;
>
> if codelist_ordinal le 2 ; /* testing */
> by codelist_ordinal;
>
> if _n_=1 then do;
> firstline='proc format ; ';
> put firstline;
> end;
>
> if first.codelist_ordinal then do;
> fmtnameline='value '||sasformatname;
> put fmtnameline;
> end;
> /* fixed by putting LINES= here *******/
> lines= codedvalue || '="' ||trim(translatedtext)||'"';
> put lines;
> if last.codelist_ordinal then do;
> endvalue=';';
> put endvalue;
> end;
>
> run;
>
> proc format ;
> value YES_NO
> 1 ="Yes"
> 0 ="No"
> .I ="NI"
> .A ="NA"
> .U ="UNK"
> ;
> .O ="OTH"
> value NEUROPAT
> 0 ="Grade0"
> 1 ="Grade1"
> 2 ="Grade2"
> 3 ="Grade3"
> 4 ="Grade4"
> .I ="NI"
> .A ="NA"
> .U ="UNK"
> ;
> .O ="OTH"
> NOTE: There were 5025 observations read from the data set
> WORK.CODE_FMTS.
> NOTE: DATA statement used (Total process time):
> real time 0.00 seconds
> cpu time 0.00 seconds

And this is what I'm now seeing in the log...not what was in post 1
and #2

proc format ;
value YES_NO
1 ="Yes"
0 ="No"
..I ="NI"
..A ="NA"
..U ="UNK"
..O ="OTH"
;
value NEUROPAT
0 ="Grade0"
1 ="Grade1"
2 ="Grade2"
3 ="Grade3"
4 ="Grade4"
..I ="NI"
..A ="NA"
..U ="UNK"
..O ="OTH"
;

....sorry folks....it's getting late here

From: data _null_; on 16 Apr 2010 11:36

On Apr 15, 9:42 pm, Bill McKirgan <bill.mckir...(a)gmail.com> wrote:
> On Apr 15, 9:40 pm, Bill McKirgan <bill.mckir...(a)gmail.com> wrote:
>
>
>
>
>
> > On Apr 15, 9:35 pm, Bill McKirgan <bill.mckir...(a)gmail.com> wrote:
>
> > > I'm stumped, but very close to the solution I see...at least I think
> > > so.
>
> > > I have a collection of metadata that I am trying to transform into SAS
> > > format statements. The data are nicely organized, coming from an
> > > OpenClinica implementation of CDISC, and I am at the final stages of
> > > telling SAS how to write the lines of code that I want to save to text
> > > files for later use.
>
> > > The following sample is close to what I want (a series of valid proc
> > > format VALUE statements); however, the closing semicolon IS MISPLACED..
> > > So, here's my code:
>
> > > data _null_; set code_fmts;
>
> > > if codelist_ordinal le 2 ; /* testing */
> > > by codelist_ordinal;
>
> > > if _n_=1 then do;
> > > firstline='proc format ; ';
> > > put firstline;
> > > end;
>
> > > if first.codelist_ordinal then do;
> > > fmtnameline='value '||sasformatname;
> > > put fmtnameline;
> > > end;
>
> > > if last.codelist_ordinal then do;
> > > endvalue=';';
> > > put endvalue;
> > > end;
>
> > > lines= codedvalue || '="' ||trim(translatedtext)||'"';
> > > put lines;
> > > run;
>
> > > And here's the log...
>
> > > proc format ;
> > > value YES_NO
> > > 1 ="Yes"
> > > 0 ="No"
> > > .I ="NI"
> > > .A ="NA"
> > > .U ="UNK"
> > > ;
> > > .O ="OTH"
> > > value NEUROPAT
> > > 0 ="Grade0"
> > > 1 ="Grade1"
> > > 2 ="Grade2"
> > > 3 ="Grade3"
> > > 4 ="Grade4"
> > > .I ="NI"
> > > .A ="NA"
> > > .U ="UNK"
> > > ;
> > > .O ="OTH"
> > > NOTE: There were 5025 observations read from the data set
> > > WORK.CODE_FMTS.
> > > NOTE: DATA statement used (Total process time):
> > > real time 0.00 seconds
> > > cpu time 0.00 seconds
>
> > > Of course I want the semicolon to FOLLOW the last value in each VALUE
> > > statement.
>
> > > Can someone suggest a way for me to fix this?
>
> > > Thank you in advance.
>
> > > Bill McKirgan
>
> > Doah...nevermind
>
> > data _null_; set code_fmts;
>
> > if codelist_ordinal le 2 ; /* testing */
> > by codelist_ordinal;
>
> > if _n_=1 then do;
> > firstline='proc format ; ';
> > put firstline;
> > end;
>
> > if first.codelist_ordinal then do;
> > fmtnameline='value '||sasformatname;
> > put fmtnameline;
> > end;
> > /* fixed by putting LINES= here *******/
> > lines= codedvalue || '="' ||trim(translatedtext)||'"';
> > put lines;
> > if last.codelist_ordinal then do;
> > endvalue=';';
> > put endvalue;
> > end;
>
> > run;
>
> > proc format ;
> > value YES_NO
> > 1 ="Yes"
> > 0 ="No"
> > .I ="NI"
> > .A ="NA"
> > .U ="UNK"
> > ;
> > .O ="OTH"
> > value NEUROPAT
> > 0 ="Grade0"
> > 1 ="Grade1"
> > 2 ="Grade2"
> > 3 ="Grade3"
> > 4 ="Grade4"
> > .I ="NI"
> > .A ="NA"
> > .U ="UNK"
> > ;
> > .O ="OTH"
> > NOTE: There were 5025 observations read from the data set
> > WORK.CODE_FMTS.
> > NOTE: DATA statement used (Total process time):
> > real time 0.00 seconds
> > cpu time 0.00 seconds
>
> And this is what I'm now seeing in the log...not what was in post 1
> and #2
>
> proc format ;
> value YES_NO
> 1 ="Yes"
> 0 ="No"
> .I ="NI"
> .A ="NA"
> .U ="UNK"
> .O ="OTH"
> ;
> value NEUROPAT
> 0 ="Grade0"
> 1 ="Grade1"
> 2 ="Grade2"
> 3 ="Grade3"
> 4 ="Grade4"
> .I ="NI"
> .A ="NA"
> .U ="UNK"
> .O ="OTH"
> ;
>
> ...sorry folks....it's getting late here- Hide quoted text -
>
> - Show quoted text -

Good to see you got the program working. Why not use a CONTROL data
set with CNTLIN option in proc format to create the formats?

Of course the way you are doing it is exactly the same way it is done
at many PHARMAS. In fact the code is probably %INC in each an every
program. I also used the program when I want to "browse the
formats", but %INCing every time is somewhat inefficient.

For the code GEN I would suggest that you at least take advantage of
the QUOTE function. QUOTE will accommodate quotes in the values.
Also, I would take more advantage of the PUT statements features to
make the GENed code look nicer.

For example:

data fmts;
infile datalines dsd;
informat codelist_ordinal codedvalue translatedtext $32.;
input codelist_ordinal codedvalue translatedtext;
datalines4;
NEUROPAT,.A,NA
NEUROPAT,.I,NI
NEUROPAT,.O,OTH
NEUROPAT,.U,UNK
NEUROPAT,0,Grade0
NEUROPAT,1,Grade"1
NEUROPAT,2,Grade2
NEUROPAT,3,Grade3
NEUROPAT,4,Grade4
YES_NO,.A,NA
YES_NO,.I,NI
YES_NO,.O,OTH
YES_NO,.U,UNK Ya'll
YES_NO,0,No
YES_NO,1,Yes
$TRT,A,Placebo
$TRT,B,Active
;;;;
run;
data _null_;
file log column=c;
put 'proc format;';
do until(eof);
do until(last.codelist_ordinal);
set fmts end=eof;
by notsorted codelist_ordinal;
if first.codelist_ordinal then do;
type = ifC(codelist_ordinal eq: '$','C','N');
put +3 'value ' codelist_ordinal;
end;
select(type);
when('C') put +6 codedvalue :$quote32. @;
when('N') put +6 codedvalue @;
end;
put @(max(c,10)) ' = ' translatedtext :$quote258.;
end;
put +6 ';' /;
end;
put +3 'Run;';
stop;
run;

From: Bill McKirgan on 16 Apr 2010 12:09

On Apr 16, 10:36 am, "data _null_;" <datan...(a)gmail.com> wrote:
> On Apr 15, 9:42 pm, Bill McKirgan <bill.mckir...(a)gmail.com> wrote:
>
>
>
>
>
> > On Apr 15, 9:40 pm, Bill McKirgan <bill.mckir...(a)gmail.com> wrote:
>
> > > On Apr 15, 9:35 pm, Bill McKirgan <bill.mckir...(a)gmail.com> wrote:
>
> > > > I'm stumped, but very close to the solution I see...at least I think
> > > > so.
>
> > > > I have a collection of metadata that I am trying to transform into SAS
> > > > format statements. The data are nicely organized, coming from an
> > > > OpenClinica implementation of CDISC, and I am at the final stages of
> > > > telling SAS how to write the lines of code that I want to save to text
> > > > files for later use.
>
> > > > The following sample is close to what I want (a series of valid proc
> > > > format VALUE statements); however, the closing semicolon IS MISPLACED.
> > > > So, here's my code:
>
> > > > data _null_; set code_fmts;
>
> > > > if codelist_ordinal le 2 ; /* testing */
> > > > by codelist_ordinal;
>
> > > > if _n_=1 then do;
> > > > firstline='proc format ; ';
> > > > put firstline;
> > > > end;
>
> > > > if first.codelist_ordinal then do;
> > > > fmtnameline='value '||sasformatname;
> > > > put fmtnameline;
> > > > end;
>
> > > > if last.codelist_ordinal then do;
> > > > endvalue=';';
> > > > put endvalue;
> > > > end;
>
> > > > lines= codedvalue || '="' ||trim(translatedtext)||'"';
> > > > put lines;
> > > > run;
>
> > > > And here's the log...
>
> > > > proc format ;
> > > > value YES_NO
> > > > 1 ="Yes"
> > > > 0 ="No"
> > > > .I ="NI"
> > > > .A ="NA"
> > > > .U ="UNK"
> > > > ;
> > > > .O ="OTH"
> > > > value NEUROPAT
> > > > 0 ="Grade0"
> > > > 1 ="Grade1"
> > > > 2 ="Grade2"
> > > > 3 ="Grade3"
> > > > 4 ="Grade4"
> > > > .I ="NI"
> > > > .A ="NA"
> > > > .U ="UNK"
> > > > ;
> > > > .O ="OTH"
> > > > NOTE: There were 5025 observations read from the data set
> > > > WORK.CODE_FMTS.
> > > > NOTE: DATA statement used (Total process time):
> > > > real time 0.00 seconds
> > > > cpu time 0.00 seconds
>
> > > > Of course I want the semicolon to FOLLOW the last value in each VALUE
> > > > statement.
>
> > > > Can someone suggest a way for me to fix this?
>
> > > > Thank you in advance.
>
> > > > Bill McKirgan
>
> > > Doah...nevermind
>
> > > data _null_; set code_fmts;
>
> > > if codelist_ordinal le 2 ; /* testing */
> > > by codelist_ordinal;
>
> > > if _n_=1 then do;
> > > firstline='proc format ; ';
> > > put firstline;
> > > end;
>
> > > if first.codelist_ordinal then do;
> > > fmtnameline='value '||sasformatname;
> > > put fmtnameline;
> > > end;
> > > /* fixed by putting LINES= here *******/
> > > lines= codedvalue || '="' ||trim(translatedtext)||'"';
> > > put lines;
> > > if last.codelist_ordinal then do;
> > > endvalue=';';
> > > put endvalue;
> > > end;
>
> > > run;
>
> > > proc format ;
> > > value YES_NO
> > > 1 ="Yes"
> > > 0 ="No"
> > > .I ="NI"
> > > .A ="NA"
> > > .U ="UNK"
> > > ;
> > > .O ="OTH"
> > > value NEUROPAT
> > > 0 ="Grade0"
> > > 1 ="Grade1"
> > > 2 ="Grade2"
> > > 3 ="Grade3"
> > > 4 ="Grade4"
> > > .I ="NI"
> > > .A ="NA"
> > > .U ="UNK"
> > > ;
> > > .O ="OTH"
> > > NOTE: There were 5025 observations read from the data set
> > > WORK.CODE_FMTS.
> > > NOTE: DATA statement used (Total process time):
> > > real time 0.00 seconds
> > > cpu time 0.00 seconds
>
> > And this is what I'm now seeing in the log...not what was in post 1
> > and #2
>
> > proc format ;
> > value YES_NO
> > 1 ="Yes"
> > 0 ="No"
> > .I ="NI"
> > .A ="NA"
> > .U ="UNK"
> > .O ="OTH"
> > ;
> > value NEUROPAT
> > 0 ="Grade0"
> > 1 ="Grade1"
> > 2 ="Grade2"
> > 3 ="Grade3"
> > 4 ="Grade4"
> > .I ="NI"
> > .A ="NA"
> > .U ="UNK"
> > .O ="OTH"
> > ;
>
> > ...sorry folks....it's getting late here- Hide quoted text -
>
> > - Show quoted text -
>
> Good to see you got the program working. Why not use a CONTROL data
> set with CNTLIN option in proc format to create the formats?
>
> Of course the way you are doing it is exactly the same way it is done
> at many PHARMAS. In fact the code is probably %INC in each an every
> program. I also used the program when I want to "browse the
> formats", but %INCing every time is somewhat inefficient.
>
> For the code GEN I would suggest that you at least take advantage of
> the QUOTE function. QUOTE will accommodate quotes in the values.
> Also, I would take more advantage of the PUT statements features to
> make the GENed code look nicer.
>
> For example:
>
> data fmts;
> infile datalines dsd;
> informat codelist_ordinal codedvalue translatedtext $32.;
> input codelist_ordinal codedvalue translatedtext;
> datalines4;
> NEUROPAT,.A,NA
> NEUROPAT,.I,NI
> NEUROPAT,.O,OTH
> NEUROPAT,.U,UNK
> NEUROPAT,0,Grade0
> NEUROPAT,1,Grade"1
> NEUROPAT,2,Grade2
> NEUROPAT,3,Grade3
> NEUROPAT,4,Grade4
> YES_NO,.A,NA
> YES_NO,.I,NI
> YES_NO,.O,OTH
> YES_NO,.U,UNK Ya'll
> YES_NO,0,No
> YES_NO,1,Yes
> $TRT,A,Placebo
> $TRT,B,Active
> ;;;;
> run;
> data _null_;
> file log column=c;
> put 'proc format;';
> do until(eof);
> do until(last.codelist_ordinal);
> set fmts end=eof;
> by notsorted codelist_ordinal;
> if first.codelist_ordinal then do;
> type = ifC(codelist_ordinal eq: '$','C','N');
> put +3 'value ' codelist_ordinal;
> end;
> select(type);
> when('C') put +6 codedvalue :$quote32. @;
> when('N') put +6 codedvalue @;
> end;
> put @(max(c,10)) ' = ' translatedtext :$quote258.;
> end;
> put +6 ';' /;
> end;
> put +3 'Run;';
> stop;
> run;- Hide quoted text -
>
> - Show quoted text -

Thanks Data _Null_,

The example you provided is much nicer than what I was developing. It
was easy for me to change it to route the ouput to a file I can %INC,
and I just switched the VALUE to SASFORMATNAME to get the desired
format names instead of the CODELIST_ORDINAL number.

data _null_;
file "&LISTPATH.\my_SAS_FORMAT_STATEMENTS.SAS" column=c;
put 'proc format;';
do until(eof);
do until(last.codelist_ordinal);
set CODE_fmts end=eof;
by notsorted codelist_ordinal;
if first.codelist_ordinal then do;
type = ifC(codelist_ordinal eq: '$','C','N');
put +3 'value ' SASFORMATNAME;
end;
select(type);
when('C') put +6 codedvalue :$quote32. @;
when('N') put +6 codedvalue @;
end;
put @(max(c,10)) ' = ' translatedtext :$quote258.;
end;
put +6 ';' /;
end;
put +3 'Run;';
stop;
run;

This is very helpful and will allow me to quickly get some important
work done. Later I will go back and work in applying the more
efficient solution you recommend using a CONTROL dataset. I will in
fact be doing the opposite by making the format catalog file and then
use CTNLOUT to create the control dataset that will be updated as our
study evolves. At least that's my plan for now.

Thanks for your ideas and for the super-elegant code example.

--Bill McKirgan

| Next | Last
Pages: 1 2
Prev: Quotes in a -SYSPARM value
Next: how to get the code lines into the program editor window?