About Cyndia

Cyndia · ‎05-17-2012

works for me! thanks.

Cyndia · ‎05-17-2012

Hi the have dataset is like the following (the name Liz has one blank space in front of it; Sam has / in front of it): name city state Liz NY NY /Sam SJ NJ Norm LA CA Gloria SC MN want to have the first non-alphabet character removed, so the table will be : name city state Liz NY NY Sam SJ NJ Norm LA CA Gloria SC MN what do you use to remove the unwanted characters/spaces? Thanks.

Cyndia · ‎05-14-2012

thanks to Linlin and Astounding

Cyndia · ‎05-14-2012

yeah! it works.

Cyndia · ‎05-14-2012

I tried the following, the result table has 4 columns, class, major, student, and class location, but all the classes are removed, class column became empty...why? data want; length class $10.; set have; array _c(*) class:; do _n_=1 to dim(_c) while (_c(_n_) ne ' '); class=_c(_n_); end; output; drop class1 class2 class3 class4; run;

Cyndia · ‎05-14-2012

I have a table like this, but would like to consolidate all the class1, class2, class3, class4 columns into one column only: Major student class1 class2 class3 class4 class location AB Jack english math campus1 AB Ken art PE math english campus2 AB Ken history campus1 CD Mike english history campus3 CD Nancy art campus2 Want to transform the above table into this: Major student class class location AB Jack english campus1 AB Jack math campus1 AB Ken art campus2 AB Ken PE campus2 AB Ken math campus2 AB Ken english campus2 AB Ken history campus1 CD Mike english campus3 CD Mike history campus3 CD Nancy art campus2 what is the best way to do that, and save the new table as a sas table only? Thanks!

Cyndia · ‎04-25-2012

if Kent belongs to both SAS and tennis clubs, and there is another observation such as the following, can Kent have a value of 1 under tennis, too? don't have to merge name, gpa and city into one cell. cards; SAS Kent 3.0 LA tennis Kent 3.0 LA tennis Lisa 4.0 NY basketball Steve 2.9 NJ SAS Abbie 3.2 NJ basketball George 3.1 NY want to have: Obs name gpa city SAS tennis basketball 1 Kent 3.0 LA 1 1 . 2 Lisa 4.0 NY . 1 . 3 Steve 2.9 NJ . . 1 4 Abbie 3.2 NJ 1 . . 5 George 3.1 NY . . 1 ==== === ====== ========== 16.2 2 2 2

Cyndia · ‎04-25-2012

yes, I want to have one column for each club with a flag if a person belongs to that club as you described. and it's possible that a person belongs to more than one club. and want to keep the GPA and City in the same column as name is in. so what it looks like above is exactly what it's needed. I think db2 sql has a way to put name, GPA and CITy into the same column, but not sure how to extend the table so it has the club membership info to the right.

Cyndia · ‎04-25-2012

would like to create additional columns with dataset like the following: club name GPA City SAS Kent 3.0 LA tennis Lisa 4.0 NY basketball Steve 2.9 NJ SAS Steve 2.9 NJ SAS Abbie 3.2 NJ basketball George 3.1 NY tennis George 3.1 NY want the result to look like the following: ( the very last row to have a count of all the members in a particular club) member info SAS tennis basketball name Kent GPA 3.0 City LA X name Lisa GPA 4.0 city NY X name Steve GPA 2.9 city NJ X X name Abbie GPA 3.2 City NJ X name George GPA 3.1 City NY X X total 3 2 2 it is possible to create clubs columns SAS, basketball, tennis individually, but what is the best way to create additional club columns using proc sql or other proc methods? thanks.

Cyndia · ‎04-20-2012

In a dataset when there are a mixture of date formats as the following: event holidays New Year 01/01/2012 New Year 01012012 New Year 112012 note the last one has all the zeros missing. what are the methods to re-format all the dates in the column, holidays, to have a format of mm/dd/yyyy? thanks.

Cyndia · ‎04-20-2012

what is the best way to eliminate the duplicate records when there is a dataset like this: firstname middle lastname ID age school city Debbie R Popular 12 13 xyz NY Debbie R Popular 21 13 xyz NY Debbie R Popular 12 13 xyz NY Deb R Popular 12 13 xyz NY note that only first and third records are duplicate, others are not the same. we only want to eliminate either the first or the third record in this case. there is a distinct( ) in SQL, in SAS, is there a way to eliminate only the records that are duplicate in all columns? thanks.

Cyndia · ‎04-18-2012

the following works: data xyz2; set xyz1; newdate=put(olddate, mmddyyn.); run; thanks.

Cyndia · ‎04-18-2012

data xyz2; set xyz1; format olddate mmddyyn.; run; /*Now the olddate changed from mm/dd/yyyy to mmddyyyy in new dataset xyz2 but the variable type is still numeric. the question is how to change the mmddyyyy to character type? */ /* i tried the following to change oldate to a new column named newdate and change it to character type, but newdate became negative value that does not look like a date but a number only. how to make mmddyyyy from numeric to characters mmddyyyy? */ newdate=put(olddate, $8.);

Cyndia · ‎04-18-2012

the date converted mmddyyyyy seems to be still numeric. is there a way to change it to Character mmddyyyy ? i tried ndob=put(dob, $8.); the ndob became negative number that does not look like a date any more.

Cyndia · ‎04-18-2012

Linlin, thanks! it's perfect.

Online Status	Offline
Date Last Visited	‎09-01-2015 07:11 AM

Re: removing first unwanted/non alphabet characters in a column

removing first unwanted/non alphabet characters in a column

Re: consolidating columns

Re: consolidating columns

Re: consolidating columns

consolidating columns

Re: creating additional columns with proc sql

Re: creating additional columns with proc sql

creating additional columns with proc sql

how to re-format dates

Re: removing first unwanted/non alphabet characters in a column

removing first unwanted/non alphabet characters in a column

Re: consolidating columns

Re: consolidating columns

Re: consolidating columns

consolidating columns

Re: creating additional columns with proc sql

Re: creating additional columns with proc sql

creating additional columns with proc sql

how to re-format dates

elimination of duplicate rows

Re: changing from a date format "mm/dd/yyyy" to character format "mmdd...

Re: changing from a date format "mm/dd/yyyy" to character format "mmdd...

Re: changing from a date format "mm/dd/yyyy" to character format "mmdd...

Re: changing from a date format "mm/dd/yyyy" to character format "mmdd...