Exporting var names as Excel columns and value labels as Excel drop down lists.
Locked
Exporting var names as Excel columns and value labels as Excel drop down lists.
 
|
Humanitarian NGO's are often all or mostly volunteer and have tight budgets.
They would not have access to SPSS itself. Maybe Excel on laptops/tablets could be used in data gathering. ===== Given something like this data list list/ ID (f4) Name (a10) Gender (f1) EyeColor (f1). begin data. end data. VARIABLE LABELS ID "Respondent's unique ID" Name 'first or given name' Gender 'Self-identified gender' EyeColor "Eye color on driver's license". VALUE LABELS Gender 1 'Female' 2 'Male' / EyeColor 1 'Brown' 2 'Blue' 3 'Green' 4 'Mixed' -1 'not answered'. ========== I am looking for a way to export this to Excel with -- a front sheet with the variable names as column headers (already have this) -- The value labels as drop-down lists in the cells of a column. -- a second sheet with 2 columns VariableName and VarLabel in quote marks. 1 row per variable. As a pipe-dream addon, hovering on the column header would bring up a "hint" that was the variable label. ----- Art Kendall Social Research Consultants -- Sent from: http://spssx-discussion.1045642.n5.nabble.com/ ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD
Art Kendall
Social Research Consultants |
Re: Exporting var names as Excel columns and value labels as Excel drop down lists.
|
I've been lurking on the listserv looking for a response. I have the same
issue, organizations gathering data using Excel apps on mobile devices. I suspect that this may remain a dream for both of us, Art. Have a nice weekend, everyone. Stay safe. Brian -- Sent from: http://spssx-discussion.1045642.n5.nabble.com/ ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD |
Re: Exporting var names as Excel columns and value labels as Excel drop down lists.
Administrator
|
Hello Brian & Art. I think that some of what Art is asking for can be done
fairly easily. This part... -- The value labels as drop-down lists in the cells of a column. As a pipe-dream addon, hovering on the column header would bring up a "hint" that was the variable label. ... can be achieved using Data Validation in Excel, but I only know how to set that up manually. As for the other parts, see if this gives the desired result. I've added a 3rd worksheet that has the value labels. (It might facilitate the manual entry of that info into the Data Validation dialogs via copy-paste.) HTH. * Modify file handle as needed. FILE HANDLE ExcelFile /NAME='C:/Temp/AK.xlsx'. * Generate dataset with variables, variable labels & value labels. NEW FILE. DATASET CLOSE ALL. data list list/ ID (f4) Name (a10) Gender (f1) EyeColor (f1). begin data. end data. DATASET NAME raw. VARIABLE LABELS ID "Respondent's unique ID" Name 'first or given name' Gender 'Self-identified gender' EyeColor "Eye color on driver's license". VALUE LABELS Gender 1 'Female' 2 'Male' / EyeColor 1 'Brown' 2 'Blue' 3 'Green' 4 'Mixed' -1 'not answered'. * Export to Excel with variable names in first row. SAVE TRANSLATE OUTFILE="ExcelFile" /TYPE=XLS /VERSION=12 /MAP /FIELDNAMES VALUE=NAMES /CELLS=VALUES /EXCELOPTIONS SHEET='Main' /REPLACE. * OMS. DATASET DECLARE varinfo. DATASET DECLARE ValLabs. OMS /SELECT TABLES /TAG = "varinfo" /IF COMMANDS=['Sysfile Info'] SUBTYPES=['Variable Information'] /DESTINATION FORMAT=SAV NUMBERED=TableNumber_ OUTFILE='varinfo' VIEWER=YES. OMS /SELECT TABLES /TAG = "ValLabs" /IF COMMANDS=['Sysfile Info'] SUBTYPES=['Value Labels'] /DESTINATION FORMAT=SAV NUMBERED=TableNumber_ OUTFILE='ValLabs' VIEWER=YES. SYSFILE INFO raw. OMSEND TAG = ["varinfo" "ValLabs"]. * Export VariableName and VarLabel to "VarLabels" sheet in the same Excel file. DATASET ACTIVATE varinfo. DELETE VARIABLES TableNumber_ TO Label_ Position MeasurementLevel TO Alignment. RENAME VARIABLES ( Var1 Label = VariableName VarLabel ). ALTER TYPE VarLabel(A100). COMPUTE VarLabel = CONCAT('"', VarLabel, '"'). ALTER TYPE VarLabel (AMIN). LIST. SAVE TRANSLATE OUTFILE="ExcelFile" /TYPE=XLS /VERSION=12 /MAP /FIELDNAMES VALUE=NAMES /CELLS=VALUES /EXCELOPTIONS SHEET='VarLabels' /APPEND. * Export value labels to worksheet "ValLabs" in the Excel file. DATASET ACTIVATE ValLabs. DELETE VARIABLES TableNumber_ TO Label_. RENAME VARIABLES ( Var1 Var2 = VariableName Value). SAVE TRANSLATE OUTFILE="ExcelFile" /TYPE=XLS /VERSION=12 /MAP /FIELDNAMES VALUE=NAMES /CELLS=VALUES /EXCELOPTIONS SHEET='ValLabs' /APPEND. Brian Dates wrote > I've been lurking on the listserv looking for a response. I have the same > issue, organizations gathering data using Excel apps on mobile devices. I > suspect that this may remain a dream for both of us, Art. > > Have a nice weekend, everyone. Stay safe. > > Brian > > > > -- > Sent from: http://spssx-discussion.1045642.n5.nabble.com/ > > ===================== > To manage your subscription to SPSSX-L, send a message to > LISTSERV@.UGA > (not to SPSSX-L), with no body text except the > command. To leave the list, send the command > SIGNOFF SPSSX-L > For a list of commands to manage subscriptions, send the command > INFO REFCARD ----- -- Bruce Weaver [hidden email] http://sites.google.com/a/lakeheadu.ca/bweaver/ "When all else fails, RTFM." NOTE: My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above. -- Sent from: http://spssx-discussion.1045642.n5.nabble.com/ ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD
--
Bruce Weaver bweaver@lakeheadu.ca http://sites.google.com/a/lakeheadu.ca/bweaver/ "When all else fails, RTFM." PLEASE NOTE THE FOLLOWING: 1. My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above. 2. The SPSSX Discussion forum on Nabble is no longer linked to the SPSSX-L listserv administered by UGA (https://listserv.uga.edu/). |
Re: Exporting var names as Excel columns and value labels as Excel drop down lists.
|
|
Thank you.
I tried your syntax on the example data. I'll try it on a bigger data set in the next few days. The results look good. IIRC there used to be a command WRITE FILEINFO That command would produce a set of "cards" that could be used across brands. It appears that your syntax can be kept in a file that one could INCLUDE. ----- Art Kendall Social Research Consultants -- Sent from: http://spssx-discussion.1045642.n5.nabble.com/ ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD
Art Kendall
Social Research Consultants |
Re: Exporting var names as Excel columns and value labels as Excel drop down lists.
|
|
In reply to this post by Art Kendall
Thanks again, Bruce.
I did try it. It worked very well. It would be great if SPSS were to develop a feature. For those who will use this a few comments. FILE HANDLE ExcelFile /NAME= xxxxx. * When you do not want actual data to go to Excel try something like this. * One might want an empty shell for class assignments or data-gathering, etc. DATASET COPY EmptyShell. DATASET ACTIVATE EmptyShell. SELECT IF 1 EQ 2. EXECUTE. In any case, there is a tweak. * v Change dataset name. *SYSFILE INFO raw. SYSFILE INFO EmptyShell. In Excel, there were some '"' tags and others. The Replace in Excel worked. ----- Art Kendall Social Research Consultants -- Sent from: http://spssx-discussion.1045642.n5.nabble.com/ ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD
Art Kendall
Social Research Consultants |
Re: Exporting var names as Excel columns and value labels as Excel drop down lists.
|
|
In reply to this post by Art Kendall
Bruce, This is great! Thank-you very much for the time it took to do this. Hope the Great White North is having a good summer! Brian Brian G. Dates, M.A. Consultant in Program Evaluation, Research, and Statistics 248-229-2865 email:[hidden email] email: [hidden email] On Sat, Jul 25, 2020 at 3:04 PM Art Kendall <[hidden email]> wrote: Thank you. |
Re: Exporting var names as Excel columns and value labels as Excel drop down lists.
|
Administrator
|
No worries, Brian. If you can find someone who knows how to program visual
basic in Excel, you might even be able to use info in the ValLabs worksheet to get the drop-down (data validation) lists Art asked about. ;-) Regarding summer in the GWN, it's certainly not a normal summer. As you can imagine, the fishing lodges and other businesses that depend on visitors from USA are struggling. But things could always be (a lot) worse. In this particular area of NW Ontario (i.e., Thunder Bay District Health Unit catchment area), we're still under 100 total cases of COVID-19, and there has (so far) been only 1 death attributed to it. https://www.tbdhu.com/coviddata Cheers, Bruce Brian Dates wrote > Bruce, > > This is great! Thank-you very much for the time it took to do this. Hope > the Great White North is having a good summer! > > Brian > > Brian G. Dates, M.A. > Consultant in Program Evaluation, Research, and Statistics > 248-229-2865 > email: > briandates@ > email: > datesb@ > Website: https://sites.google.com/view/briandates/home > > > > On Sat, Jul 25, 2020 at 3:04 PM Art Kendall < > Art@ > > wrote: > >> Thank you. >> >> I tried your syntax on the example data. I'll try it on a bigger data >> set >> in the next few days. >> The results look good. >> >> IIRC there used to be a command >> WRITE FILEINFO >> That command would produce a set of "cards" that could be used across >> brands. >> >> It appears that your syntax can be kept in a file that one could INCLUDE. >> >> >> >> ----- >> Art Kendall >> Social Research Consultants >> -- >> Sent from: http://spssx-discussion.1045642.n5.nabble.com/ >> >> ===================== >> To manage your subscription to SPSSX-L, send a message to >> > LISTSERV@.UGA > (not to SPSSX-L), with no body text except the >> command. To leave the list, send the command >> SIGNOFF SPSSX-L >> For a list of commands to manage subscriptions, send the command >> INFO REFCARD >> > > ===================== > To manage your subscription to SPSSX-L, send a message to > LISTSERV@.UGA > (not to SPSSX-L), with no body text except the > command. To leave the list, send the command > SIGNOFF SPSSX-L > For a list of commands to manage subscriptions, send the command > INFO REFCARD ----- -- Bruce Weaver [hidden email] http://sites.google.com/a/lakeheadu.ca/bweaver/ "When all else fails, RTFM." NOTE: My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above. -- Sent from: http://spssx-discussion.1045642.n5.nabble.com/ ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD
--
Bruce Weaver bweaver@lakeheadu.ca http://sites.google.com/a/lakeheadu.ca/bweaver/ "When all else fails, RTFM." PLEASE NOTE THE FOLLOWING: 1. My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above. 2. The SPSSX Discussion forum on Nabble is no longer linked to the SPSSX-L listserv administered by UGA (https://listserv.uga.edu/). |
|
|
Thanks again, Bruce. I'll look into the visual basic approach. Glad you're doing well.
Brian
From: SPSSX(r) Discussion <[hidden email]> on behalf of Bruce Weaver <[hidden email]>
Sent: Monday, July 27, 2020 9:09 AM To: [hidden email] <[hidden email]> Subject: Re: Exporting var names as Excel columns and value labels as Excel drop down lists. No worries, Brian. If you can find someone who knows how to program visual
basic in Excel, you might even be able to use info in the ValLabs worksheet to get the drop-down (data validation) lists Art asked about. ;-) Regarding summer in the GWN, it's certainly not a normal summer. As you can imagine, the fishing lodges and other businesses that depend on visitors from USA are struggling. But things could always be (a lot) worse. In this particular area of NW Ontario (i.e., Thunder Bay District Health Unit catchment area), we're still under 100 total cases of COVID-19, and there has (so far) been only 1 death attributed to it. https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.tbdhu.com%2Fcoviddata&data=02%7C01%7Cbdates%40SWSOL.ORG%7Cfb46119295e44c89339708d8322e49a0%7Cecdd61640dbd4227b0986de8e52525ca%7C0%7C0%7C637314521678938402&sdata=W1Rva2cz%2BntCwPVXjt%2Fy%2FRUZ5ckSXnzXLGMPIsh6wP8%3D&reserved=0 Cheers, Bruce Brian Dates wrote > Bruce, > > This is great! Thank-you very much for the time it took to do this. Hope > the Great White North is having a good summer! > > Brian > > Brian G. Dates, M.A. > Consultant in Program Evaluation, Research, and Statistics > 248-229-2865 > email: > briandates@ > email: > datesb@ > Website: https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Fsites.google.com%2Fview%2Fbriandates%2Fhome&data=02%7C01%7Cbdates%40SWSOL.ORG%7Cfb46119295e44c89339708d8322e49a0%7Cecdd61640dbd4227b0986de8e52525ca%7C0%7C0%7C637314521678938402&sdata=kiLNxo%2Fk87oNMrNXRS3iK%2F%2FWELzGFSvPC7tOIO2SIoU%3D&reserved=0 > > > > On Sat, Jul 25, 2020 at 3:04 PM Art Kendall < > Art@ > > wrote: > >> Thank you. >> >> I tried your syntax on the example data. I'll try it on a bigger data >> set >> in the next few days. >> The results look good. >> >> IIRC there used to be a command >> WRITE FILEINFO >> That command would produce a set of "cards" that could be used across >> brands. >> >> It appears that your syntax can be kept in a file that one could INCLUDE. >> >> >> >> ----- >> Art Kendall >> Social Research Consultants >> -- >> Sent from: https://nam12.safelinks.protection.outlook.com/?url=http%3A%2F%2Fspssx-discussion.1045642.n5.nabble.com%2F&data=02%7C01%7Cbdates%40SWSOL.ORG%7Cfb46119295e44c89339708d8322e49a0%7Cecdd61640dbd4227b0986de8e52525ca%7C0%7C0%7C637314521678938402&sdata=Nsos%2BMAXlVC7h1%2BDC3a1NlhBijjW34u51pznMbUf%2FcE%3D&reserved=0 >> >> ===================== >> To manage your subscription to SPSSX-L, send a message to >> > LISTSERV@.UGA > (not to SPSSX-L), with no body text except the >> command. To leave the list, send the command >> SIGNOFF SPSSX-L >> For a list of commands to manage subscriptions, send the command >> INFO REFCARD >> > > ===================== > To manage your subscription to SPSSX-L, send a message to > LISTSERV@.UGA > (not to SPSSX-L), with no body text except the > command. To leave the list, send the command > SIGNOFF SPSSX-L > For a list of commands to manage subscriptions, send the command > INFO REFCARD ----- -- Bruce Weaver [hidden email] https://nam12.safelinks.protection.outlook.com/?url=http%3A%2F%2Fsites.google.com%2Fa%2Flakeheadu.ca%2Fbweaver%2F&data=02%7C01%7Cbdates%40SWSOL.ORG%7Cfb46119295e44c89339708d8322e49a0%7Cecdd61640dbd4227b0986de8e52525ca%7C0%7C0%7C637314521678948363&sdata=nIUBaTcPGcXg7XX%2FdLJoRNg3DC4A4vhWgKNlLPLZo2c%3D&reserved=0 "When all else fails, RTFM." NOTE: My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above. -- Sent from: https://nam12.safelinks.protection.outlook.com/?url=http%3A%2F%2Fspssx-discussion.1045642.n5.nabble.com%2F&data=02%7C01%7Cbdates%40SWSOL.ORG%7Cfb46119295e44c89339708d8322e49a0%7Cecdd61640dbd4227b0986de8e52525ca%7C0%7C0%7C637314521678948363&sdata=O2t%2B2%2FWph3ezx8XxBL1hPeSdkfVWhZo3frJjIqOjVpw%3D&reserved=0 ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD |
|
|
If you want to keep all this atomic, you can use the Statistics SaxBasic/WinwrapBasic scripting choice and load the Excel library to access those apis. Alternatively, you can use one of the Python tools that provide Python access to the Excel apis and run the Python code from Statistics. There are several such tools, but I don't know which would provide access to the Data Validation apis in Excel in order to generate the tooltips. In fact, I can't even get Excel standalone to do that trying to follow the Excel tutorial on this. :-) I can send you a Basic file that exports output tables to Excel if that would be helpful. On Mon, Jul 27, 2020 at 8:27 AM Dates, Brian <[hidden email]> wrote:
|
«
Return to SPSSX Discussion
|
1 view|%1 views
| Free forum by Nabble | Edit this page |