Sometime ago, I wrote a SASMacro that creates a copy of a SAS Data Set,
replacing all User Formatted variables by character variables of the
formats. You can then export the copy to JMP, Excel, whatever.
With JMP 6, the better way is probably to have a SAS Macro that updates JMP
columns with the new Value Label property.
At 05:20 AM 7/21/2006 -0500, you wrote:
Do any of you know of a way to use SAS formats libraries, eg .sc2 or
.sas7bcat, in JMP?
I have both SAS and JMP and regularly get SAS datasets that include
formats libraries. I can use the datafiles in JMP but have to manually
add the formats. Is there a way to use SAS to export the data with the
formats so that JMP can use them, or some other trick?
vvv CUT HERE, Since JMP-L doesn;t accept attachments, this might work vvv
%macro ExpandFormats(
/* Convert Formatted varsiables to their Character representation */
/* Param Default Description *******************************/
Data= _LAST_ , /* Input SAS Data Set name */
Out= _DATA_ , /* Output SAS Data Set */
Var= _ALL_ , /* List of variables to convert */
Format= , /* Variable/Format list */
/*********************** Tuning Parameters ****************************/
Type= USER NUMERIC, /* Type of formats to convert */
AddName= No , /* Add variable name to Variable label */
FormatL= 32 /* Default length for System Numeric Formats */
);
/* This SAS macro creates a copy of a SAS data set with numeric user- */
/* formatted variables replaced by character variables whose values */
/* are equivalent to the numeric formats. This macro can be used */
/* prior to exporting a SAS Data Set to another package in order to */
/* retain the formatted representations of the data. */
/* */
/* This SAS macro is also able to process character user-formatted */
/* variables, as well as variables formatted with SAS System formats. */
/* */
/* Written by: Kevin Thompson Agric. Stat. Lab. Univ of Ark. */
/* KThompsn@UArk.Edu */
/* Description of the paramerers */
/* Data= Specifies the Input SAS Data set. */
/* Out= Specifies the Output SAS Data set. */
/* Var= Specified a variable list of variables to convert. */
/* All of the standard variable lists allowed on a VAR */
/* statement are allowed: Var1-VarN VarA--VarB Prefix: */
/* The macro will process the subset of the variables */
/* in this list whos defined formats match the TYPE= */
/* parameter. Variables with no defined format are not */
/* converted. */
/* Format= Specified a Variable/Format list similar to a FORMAT*/
/* statement. This parameter may be used to override */
/* the format specifications embedded in the SAS Data */
/* Set. If variavles formatted with SAS SYSTEM formats */
/* are to be converted, the format width (w) should be */
/* specified (i.e. DATE8. ). */
/* Type= Specify the type of formats to process. Only */
/* variables whose formats are og the specified types */
/* will be processed. Select one or more of the */
/* folowing. */
/* USER User defined Formats */
/* SYSTEM SAS System formats */
/* NUMERIC Numeric formats */
/* CHARACTER Character formats */
/* AddName= Modifies the variable label by addind the variable */
/* name. Some packages (i.e. JMP) use the variable */
/* label instead of the variable name. */
/* NO Does not modify the variable label */
/* YES Prepends the variable name */
/* BEFORE Prepends the variable name */
/* AFTER Appends the variable name */
/* FormatL Default length for converted variables. This */
/* parameter is only used when converting a numeric */
/* variable formatted by a SAS System format that does */
/* not include the format width ( WEEKDATE. ). In this */
/* truncation of the data value is possible. */
/* Examples: */
/* %ExpandFormats(Data=In, Out=New); */
/* Copies data from Data Set IN to Data Set OUT, converting */
/* numeric variables with user defined formats to a character*/
/* variable representation */
/* %ExpandFormats(Data=In, Out=New, Var=Date:, */
/* Format=Date: MMDDYY8.. Type=Numeric System); */
/* Comverts all variables whose name starts with "DATE" to */
/* a character variable whose varles are a character */
/* reprepresentation of the date */
* Data checking ;
%If &Data= %Then %Let Data=_LAST_;
%If &Out= %Then %Let Out=_DATA_ ;
%If &Var= %Then %Let Var=_ALL_ ;
%If &Type= %Then %Let Type=USER NUMERIC ;
* Preserve Data Set name ;
%If %UPCase(&DATA)=_LAST_ %Then %Let Data=&SYSLast;
%If %UPCase(&DATA)=_NULL_ %Then %Do;
%Put ERROR: Data Set &DATA not allowed. ;
%GoTo EXIT;
%End;
%If %UPCase(&DATA)=_DATA_ %Then %Do;
%Put ERROR: Data Set &DATA not allowed. ;
%GoTo EXIT;
%End;
%If %UPCase(&DATA)=_ALL_ %Then %Do;
%Put ERROR: Data Set &DATA not allowed. ;
%GoTo EXIT;
%End;
Proc Contents Data=&Data Out=Work._ExpCon_ NoPrint MemType=DATA;
Quit;
%If &SysErr > 4 %Then %Do;
%Put ERROR: Error reading Data Set &DATA.. ;
%GoTo EXIT;
%End;
%Let Data=*&Data;
Data _Null_;
Set Work._ExpCon_(obs=1) ;
DSName = Trim(libname)|| "." || memname ;
Call SymPut( "Data" , DSName );
Run;
%If %QSubStr(&Data,1,1)=%Quote(*) %Then %Do;
%Let Data = %Substr(&Data,2);
%Put ERROR: Data Set &DATA is empty. ;
%GoTo EXIT;
%End;
%Let AddName=%UpCase(&AddName);
%Let Type =%UpCase(&Type);
%Let TypeUsr = %Index(&Type, USER );
%Let TypeSys = %Index(&Type, SYS );
%Let TypeNum = %Index(&Type, NUM );
%Let TypeChr = %Index(&Type, CHAR );
%If &TypeUsr + &TypeSys = 0 %Then %Let TypeUsr = 1;
%If &TypeNum + &TypeChr = 0 %Then %Let TypeNum = 1;
* List Selected variables in the Input SAS Data Set ;
Data Work._ExpCon1_;
Set &Data(Obs=0);
Keep &Var ;
Run;
Proc Contents Data=Work._ExpCon1_ Out=Work._ExpCon1_ Noprint;
Quit;
* List all variables and formats in the Input SAS Data Set ;
Data Work._ExpCon2_;
Set &Data(Obs=0);
Format &Format ;
Run;
Proc Contents Data=Work._ExpCon2_ Out=Work._ExpCon2_ Noprint;
Quit;
* List all known user formats ;
Proc Format CntlOut=Work._ExpFmt_ ;
Value _ExpFmt_ other=.;
Quit;
Data Work._ExpFmt_;
Set Work._ExpFmt_ ;
Where Type in ("P" "N" "C") ;
If Type='C'
Then Format="$" || FmtName;
Else Format=FmtName;
Keep Format Default Max Length ;
Rename Default=FmtDflt Max=FmtMax Length=FmtLen ;
Run;
* Combine all variables with user formats ;
Proc Sort Data=Work._ExpFmt_ NoDupKey;
By Format ;
Quit;
Proc Sort Data=Work._ExpCon2_ ;
By Format;
Quit;
Data Work._ExpCon2_ ;
Merge Work._ExpFmt_ (in=InFmt)
Work._ExpCon2_ (in=InVar) ;
By Format ;
UserFmt = InFmt ;
If InVar ;
Keep UserFmt Name Format Type Length Label FormatL FormatD FmtDflt
FmtLen FmtMax ;
Run;
* Combine all variables with selected variables ;
Proc Sort Data=Work._ExpCon1_(Keep=Name) ;
By Name ;
Quit;
Proc Sort Data=Work._ExpCon2_;
By Name ;
Quit;
Data Work._ExpCon2_;
Merge Work._ExpCon1_ (in=inVar)
Work._ExpCon2_ ;
By Name ;
KeepVar = inVar ;
Run;
* Construct Format reference ;
Data Work._ExpCon2_;
Length CvtFormat $40 ;
Length CvtLength 8 ;
Length CvtLabel $255;
Set Work._ExpCon2_ ;
If Format = ' ' & FormatL > 0 & Type=1 Then Format = 'F' ;
If Format = ' ' & FormatL > 0 & Type=2 Then Format = '$' ;
If FormatL = 0 & FmtDflt > . Then FormatL = FmtDflt ;
CvtFormat = Format;
If CvtFormat = ' '
Then CvtFormat = '.' ;
Else Do;
If FormatL = 0
Then Do;
If Type = 1
Then CvtLength = &FormatL ;
Else CvtLength = Length * 2 ;
CvtFormat = Trim(CvtFormat) || '.' ;
End;
Else Do ;
CvtLength = FormatL ;
CvtFormat = Trim(CvtFormat) || Compress(Put(FormatL,
F.)) || '.' ;
If FormatD > 0 Then
CvtFormat = Trim(CvtFormat) ||
Compress(Put(FormatD, F.)) ;
End;
End ;
If Label = ' ' Then CvtLabel = Name ;
Else Select ("&AddName");
When ("YES") CvtLabel = Trim(Name) || ' - ' || Label ;
When ("BEFORE") CvtLabel = Trim(Name) || ' - ' || Label ;
When ("AFTER") CvtLabel = Trim(Label) || ' (' || Trim(Name)
|| ")" ;
Otherwise CvtLabel = Label ;
End;
KeepFmt = 0 ;
If &TypeUsr & UserFmt=1 & &TypeNum & Type=1 Then KeepFmt=1 ;
If &TypeUsr & UserFmt=1 & &TypeChr & Type=2 Then KeepFmt=1 ;
If &TypeSys & UserFmt=0 & &TypeNum & Type=1 Then KeepFmt=1 ;
If &TypeSys & UserFmt=0 & &TypeChr & Type=2 Then KeepFmt=1 ;
If CvtFormat = '.' Then KeepFmt=0 ;
If KeepVar = 1 ;
If KeepFmt = 1 ;
Keep Name CvtFormat CvtLength CvtLabel ;
Run;
Proc SQL;
Reset NoPrint;
/* List Variable names in a data set */
SELECT Count(Name),
Name,
Trim(CvtLabel),
CvtFormat,
CvtLength
INTO :VarCount ,
:VarNames SEPARATED BY ' |' ,
:VarLabels SEPARATED BY ' |' ,
:VarFmts SEPARATED BY ' |' ,
:VarLens SEPARATED BY ' |'
FROM Work._ExpCon2_
Quit;
%Let VarLabels=%SuperQ(VarLabels) ;
Data &Out ;
Set &Data;
%Do i = 1 %To &VarCount;
%Let Name = %Scan(&VarNames , &i, | );
%Let Label=%QScan(&VarLabels , &i, | );
%Let Fmt = %Scan(&VarFmts , &i, | );
%Let Len = %Scan(&VarLens , &i, | );
%put Note: &i &Name &Fmt &Len &Label ;
Length __&i._ $&Len ;
Label __&i._="&Label";
__&i._=Put(&Name, &Fmt);
Drop &Name;
Rename __&i._=&Name ;
%End;
Run;
%Exit:
%Mend;
^^^ CUT HERE ^^^
Kevin C Thompson
Agri Stat Lab
AGRX 105
Univ of Arkansas
KThompsn@UArk.Edu
(479) 575-2448
|