f



Re: question on reading raw data #2

You'd need to use a UDF.  There are probably 1e8 of these available on a
quick google search; one example is
http://www.meadinkent.co.uk/excel-color-calcs.htm
Something like

Function colorofcell(ref as range)
colorofcell=ref.interior.color
end function

put in the VB editor; then you just use
=colorofcell(B2)
and it will have the RGB color; for example, "pure" red is 255.  Be aware
that any function based on color will NOT change until the value itself
changes in the cell (or the sheet is closed/reopened); just changing the
color with your mouse will not cause the function to be recalculated.  Not a
problem if you're not changing things yourself, of course.  Then do whatever
you want with that value - you could just use =IF(colorofcell(b2)=255,1,0)
for example.

In fact, all of this of course assumes that you're not the one putting red
in the cells... if you are, then do it differently (ie, don't put red in the
cells, put a value in a column!).

-Joe

On Tue, Nov 10, 2009 at 9:43 AM, mike patel <sassql@gmail.com> wrote:

> Hi Joe,
>
> You're right it's a excel file and not CSV. Now when you said create an
> excel statement to create a variable which will have values 1 or 0 based on
> the color can you tell me how do we do that?
>
> Thanks for replying back!!
>
> On Tue, Nov 10, 2009 at 10:27 AM, Joe Matise <snoopy369@gmail.com> wrote:
>
>> Can't imagine how a CSV would have color coded records in it.  CSV is just
>> comma separated values, there's no metadata beyond possibly the top line
>> headers.  Open it in notepad/textpad/etc. and you'll find no colors, I
>> suspect.
>>
>> If it's actually an excel file, which would have the ability to have color
>> coded rows, you can create an excel statement that will have a 1 or a 0 in
>> it based on the red/not red, and then use that variable.
>>
>> -Joe
>>
>> On Tue, Nov 10, 2009 at 9:00 AM, sassql <sassql@gmail.com> wrote:
>>
>>> Hi All,
>>>
>>> Hope you all are doing fine. I had a question about reading a raw data
>>> file which is a .csv file & has color coded records in it. So say for
>>> example I have records 1 through 20 color coded in red & records from
>>> may be 31 - 35 color coded in blue & so on. Now after I have imported
>>> this file in SAS I've to create a categorical variable based on the
>>> color coded records. Is there a way that we can programatically create
>>> this categorical variable just based on the color of the record?
>>>
>>> FYI: The file contains a list of medications that patients where not
>>> supposed to take during the course of study. So if I somehow cannot
>>> identify these records based on color I'll have to manually enter the
>>> name of each drug in a 'If else' statement & then create the
>>> categorical variable instead.
>>>
>>> Thanks in advance!!
>>>
>>
>>
>
0
snoopy369 (1724)
11/10/2009 4:28:24 PM
comp.soft-sys.sas 142828 articles. 3 followers. Post Follow

1 Replies
380 Views

Similar Articles

[PageSpeed] 13

Hi Joe,

Thank you so much for your help. This worked :)!!


On Nov 10, 11:28=A0am, snoopy...@GMAIL.COM (Joe Matise) wrote:
> You'd need to use a UDF. =A0There are probably 1e8 of these available on =
a
> quick google search; one example ishttp://www.meadinkent.co.uk/excel-colo=
r-calcs.htm
> Something like
>
> Function colorofcell(ref as range)
> colorofcell=3Dref.interior.color
> end function
>
> put in the VB editor; then you just use
> =3Dcolorofcell(B2)
> and it will have the RGB color; for example, "pure" red is 255. =A0Be awa=
re
> that any function based on color will NOT change until the value itself
> changes in the cell (or the sheet is closed/reopened); just changing the
> color with your mouse will not cause the function to be recalculated. =A0=
Not a
> problem if you're not changing things yourself, of course. =A0Then do wha=
tever
> you want with that value - you could just use =3DIF(colorofcell(b2)=3D255=
,1,0)
> for example.
>
> In fact, all of this of course assumes that you're not the one putting re=
d
> in the cells... if you are, then do it differently (ie, don't put red in =
the
> cells, put a value in a column!).
>
> -Joe
>
>
>
> On Tue, Nov 10, 2009 at 9:43 AM, mike patel <sas...@gmail.com> wrote:
> > Hi Joe,
>
> > You're right it's a excel file and not CSV. Now when you said create an
> > excel statement to create a variable which will have values 1 or 0 base=
d on
> > the color can you tell me how do we do that?
>
> > Thanks for replying back!!
>
> > On Tue, Nov 10, 2009 at 10:27 AM, Joe Matise <snoopy...@gmail.com> wrot=
e:
>
> >> Can't imagine how a CSV would have color coded records in it. =A0CSV i=
s just
> >> comma separated values, there's no metadata beyond possibly the top li=
ne
> >> headers. =A0Open it in notepad/textpad/etc. and you'll find no colors,=
 I
> >> suspect.
>
> >> If it's actually an excel file, which would have the ability to have c=
olor
> >> coded rows, you can create an excel statement that will have a 1 or a =
0 in
> >> it based on the red/not red, and then use that variable.
>
> >> -Joe
>
> >> On Tue, Nov 10, 2009 at 9:00 AM, sassql <sas...@gmail.com> wrote:
>
> >>> Hi All,
>
> >>> Hope you all are doing fine. I had a question about reading a raw dat=
a
> >>> file which is a .csv file & has color coded records in it. So say for
> >>> example I have records 1 through 20 color coded in red & records from
> >>> may be 31 - 35 color coded in blue & so on. Now after I have imported
> >>> this file in SAS I've to create a categorical variable based on the
> >>> color coded records. Is there a way that we can programatically creat=
e
> >>> this categorical variable just based on the color of the record?
>
> >>> FYI: The file contains a list of medications that patients where not
> >>> supposed to take during the course of study. So if I somehow cannot
> >>> identify these records based on color I'll have to manually enter the
> >>> name of each drug in a 'If else' statement & then create the
> >>> categorical variable instead.
>
> >>> Thanks in advance!!- Hide quoted text -
>
> - Show quoted text -

0
sassql (82)
11/10/2009 7:06:05 PM
Reply: